Optimising MySQL Script
mysql performance tuning
mysql performance tuning innodb
optimize mysql database
mysql 5.7 performance tuning
mysql query cache
mysql query optimization
mysql config generator
I have started a new job and they are using MySQL as their main DB.
I am more use to MSSQL and was wondering if the following statement can be optimised as it's taking 10 minutes!
Thank you in advance for any help anyone can offer.
SELECT CAST(DATE_FORMAT(created_at, '%Y-%m-%d %k:%i:00') AS DATETIME) AS 'Date', COUNT(*) AS Count FROM db WHERE created_at BETWEEN NOW() - INTERVAL 5 HOUR AND NOW() AND status = 'Accepted' GROUP BY CAST(DATE_FORMAT(created_at, '%Y-%m-%d %k:%i:00') AS DATETIME)
No clue why you do the cast within the select or the group by but replace the last line by:
GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %k:%i')
If that doesn't help much, add 'EXPLAIN ' before that command, check the output. If you can't figure it out with the explain-prefix, post it here so someone can give you advice on indexes.
MySQL Performance Tuning: Tips, Scripts and Tools, 8.1 Optimization Overview · 8.2 Optimizing SQL Statements you might optimize at the level of individual SQL statements, entire applications, a single database The MySQLTuner is a script and we can use this to optimize the MySQL performance. It give suggestions for increasing server’s performance and stability.
(status, created_at) will optimise the
WHERE part of this query (constants before ranges).
Your grouping seems to indicate you want to group by the minute of
created_at. Creating a generated column based on this expression and appending this to the index would assist the
GROUP BY part of this clause (and the
GROUP BY should refer to this generated column rather than an expression).
alter table db add minute bigint unsigned as (to_seconds(created_at) div 60), add index status_created_at_min (status, created_at, minute);
And the resulting query:
SELECT CAST(DATE_FORMAT(created_at, '%Y-%m-%d %k:%i:00') AS DATETIME) AS 'Date', COUNT(*) AS Count FROM db WHERE created_at BETWEEN NOW() - INTERVAL 5 HOUR AND NOW() AND status = 'Accepted' GROUP BY minute;
MySQL 8.0 Reference Manual :: 8 Optimization, using indexes. A database index is a data structure that improves the speed of operations in a table. As your database tables grow, an increasing number of rows need to be inspected each time which can slow the overall performance of the database and respectively your application. A valid Alibaba cloud account. If you don't have one already, you can sign up for an Alibaba Cloud and enjoy $300 worth in Free Trial. A server running your favorite operating system that can support MySQL (e.g. Ubuntu, Centos, Debian). MySQL database server. A MySQL user capable of running root commands.
This is likely to speed it up some, because it will take care of the
INDEX(status, created_at) -- in this order
If you create a "generated" column (as danblack suggests), then carry it a step further by using only the generated column in the query and the index. That way this becomes a "covering" index so the query is performed entirely in the index:
INDEX(status, minute) -- in this order
If that is not enough...
Large tables with "reports" often benefit from creating and maintaining "Summary Table(s)". Unlike some other vendors, there is no builtin mechanism for doing this. See this .
MySQL optimization using indexes, The main considerations for optimizing queries are: To make a slow SELECT WHERE query faster, the first thing to check is whether you can add an index. mysql> OPTIMIZE TABLE EMPLOYEES, SALESINFO, FINANCES; While running optimization on a table, MySQL does the following tasks: Creates a temp table, Deletes the original one after optimizing it, and; Rename the temp table to the original name in the end. Post-MySQL Optimization. After finishing up with optimization, you can issue the below command.
MySQL 5.7 Reference Manual :: 8.2.1 Optimizing SELECT , Optimize EVERYTHING! some serious $$$ by optimizing my server, without sacrificing speed. Running MySQL at optimal settings for specific resources helps handle larger server loads and prevents server slowdown. Generally, after tuning Apache to handle larger loads, it is beneficial to tune MySQL to additional connections. Database tuning is an expansive topic,
major/MySQLTuner-perl: MySQLTuner is a script written in , Consider a case where you are running the following SQL query from a database with 500 rows without an index: mysql> select customer_id, MySQLTuner : a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
MySQL Performance Tuning Scripts and Know-How, Tuning MySQL Performance with MySQLTuner MySQLTuner is a Perl script that analyzes Run OPTIMIZE TABLE to defragment tables for better performance Now, lets looks at popular scripts and tools available for MySQL performance tuning: MySqlTuner, Tuning-Primer, MySQLreport, phpMyAdmin Advisor and Percona Configuration Wizard for MySQL. MySQLTuner. A script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
- You have an index on created_at? Or (created_at,status)? Also, you can lose the casting in the GROUP BY.
- Yes I have an index on both the created at and status. I have just removed the casting so it only shows... GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d %k:%i:00') now the script will not run.
- You have to remove the last line. You could try dropping the individual indices and replacing with a composite. Other than that, I can't see much room for improvement - but maybe others will have better ideas.
- Thanks for your advice. I would not know where to start with dropping indices. I will have a look on Google though so thanks. Paul
ALTER TABLE my_table DROP INDEX x; ALTER TABLE my_table DROP INDEX y; ALTER TABLE my_table ADD INDEX (x,y);
- This and drop the "CAST( AS DATETIME)". The result is returning strings and the "DATE_FORMAT()" already gives you the desired format, so the casting just slows it down for no reason. And just an advice in general: use bracelets around "x between y and z". It makes it easier to read and some database settings might cause troubles when there are no bracelets.