| Query Optimization Report
If the optimized query below returns unexpected results, click here to retrieve another alternative.
The optimal indexes for this query are ...
By default, the database cannot use indexes on columns used as function parameters, e.g. MONTH(date) = 5.
You can index the these statements by creating and indexing virtual columns:
Please create these indexes before running the optimized query:
The following indexes already exist in the provided schema and were found as useful for optimal query performance:
Explaining the magic ...
Your next optimization is just a few clicks away!
Thank you for trying our free version, we hope you got some queries optimized.
Purchase now to reduce 25% off the first month's cost.
Coupon code: EF872 (expires on September 30)
Advanced insights for Plus and Premium customers
Our advanced insights are available for Plus and Premium subscribers.
Please consider upgrading to unlock these advanced capabilities.
This feature is only available for Plus & Premium subscriptions
We have very flexible subscription plans you can upgrade to! Have a look at our pricing page, or please contact us for more information.
Create an account or login
Optimize 1 query for free!
No credit card is required.Optimizing a SQL query
Tell us about your database:
Please submit the SELECT query you would like to optimize:
Most of our advanced optimization algorithms require extra metadata from your database to effectively optimize this query (for example: the schema's structure, columns data distribution, etc.).
Read here how to quickly fetch this metadata using your favorite SQL IDE.
Once you got the metadata in a file, please upload the file here below:
* The information you provide is encrypted and protected. Learn more on our security page.
As a last step and to provide the most optimal recommendations, we need some extra metadata.
Please execute this query on your database using your SQL IDE and copy the output:
Copy(SELECT
CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),']}') AS CHAR) as metadata_json
FROM
(SELECT (@cols:=NULL),
(SELECT (0) FROM information_schema.columns cols
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"',cols.table_schema,'","table":"',cols.table_name,'","name":"', cols.column_name, '","type":"', cols.column_type, '","nullable":', IF(cols.IS_NULLABLE = 'YES', 'true', 'false'), ',"collation":"', IFNULL(cols.COLLATION_NAME, ''), '"}'))))
) cols,
(SELECT (@indexes:=NULL),
(SELECT (0) FROM information_schema.statistics indexes
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@indexes:=CONCAT_WS(',', @indexes, CONCAT('{"schema":"',indexes.table_schema,'","table":"',indexes.table_name,'","name":"', indexes.index_name, '","column":"', indexes.column_name, '","cardinality":', indexes.cardinality, ',"unique":', IF(indexes.non_unique = 1, 'false', 'true'), '}'))))
) indexes,
(SELECT (@tbls:=NULL),
(SELECT (0) FROM information_schema.tables tbls
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', `TABLE_ROWS`, ',', '"engine":"', `ENGINE`, '",', '"collation":"', `TABLE_COLLATION`, '"}'))))
tbls) x);
Please copy the output of the query from your SQL IDE and paste it here (or upload as a file):
* The information you provide is encrypted and protected. Learn more on our security page.
You're missing out! Users who submit their metadata get up to 60% better recommendations.
Resubmit a query for optimization
Please adjust the query's syntax and re-submit it for optimization:
Installing EverSQL Performance Sensor
EverSQL performance sensor will track your databases slow query logs on an on-going basis and generate personalized optimization insights for you automatically. You can use the performance sensor with MySQL, MariaDB and Percona databases.
Step 1: Enable the slow query log
The performance sensor requires slow query logs to be enabled.
Click here for instructions if you don't have them enabled.
To enable slow query logs, please add the following parameters to MySQL's configuration file. By default, the configuration file is at /etc/my.cnf, or at /etc/mysql/my.cnf.
[mysqld]
slow_query_log=1
long_query_time=1 # Logs queries longer than 1 second
log_output=FILE
slow_query_log_file=/var/lib/mysql/slow.log
To apply the changes, please restart the database (requires short downtime):
sudo /etc/init.d/mysql restart
Step 2: Install the performance sensor
To install the performance sensor, please run the following commands.
If your slow query log file is not at /var/lib/mysql/slow.log, please modify the path below.
curl https://www.eversql.com/install/ubuntu_18_04 > install_sensor
sudo bash -x install_sensor /var/lib/mysql/slow.log
Once installed, the sensor will start sending identified slow queries within a few minutes.
EverSQL performance sensor will track your databases slow query logs on an on-going basis and generate personalized optimization insights for you automatically. You can find more information here.
You can use the performance sensor with Amazon RDS MySQL / RDS MariaDB / Aurora MySQL databases.
Step 1: Enable the slow query log
To enable slow query logs on your database instances, please follow the 'Enable slow query log' section here.
Once you're done editing the parameter group and assigning it to your instance, please make sure you restart the database to apply the changes (may require downtime).
Step 2: Install the performance sensor (Beta)
You can now deploy the performance sensor from the Amazon Serverless Application repository.
Your personal API key (required as part of the installation):
Once installed, the sensor will start sending identified slow queries within a few minutes.
Uploading the database metadata
Over 60% of our advanced optimization recommendations require your recent database metadata.
Please execute this query on your database using your SQL IDE and copy the output:
Copy(SELECT
CAST(CONCAT('{"columns": [',IFNULL(@cols,''),'], "indexes": [',IFNULL(@indexes,''),'], "tables":[',IFNULL(@tbls,''),']}') AS CHAR) as metadata_json
FROM
(SELECT (@cols:=NULL),
(SELECT (0) FROM information_schema.columns cols
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@cols:=CONCAT_WS(',', @cols, CONCAT('{"schema":"',cols.table_schema,'","table":"',cols.table_name,'","name":"', cols.column_name, '","type":"', cols.column_type, '","nullable":', IF(cols.IS_NULLABLE = 'YES', 'true', 'false'), ',"collation":"', IFNULL(cols.COLLATION_NAME, ''), '"}'))))
) cols,
(SELECT (@indexes:=NULL),
(SELECT (0) FROM information_schema.statistics indexes
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@indexes:=CONCAT_WS(',', @indexes, CONCAT('{"schema":"',indexes.table_schema,'","table":"',indexes.table_name,'","name":"', indexes.index_name, '","column":"', indexes.column_name, '","cardinality":', indexes.cardinality, ',"unique":', IF(indexes.non_unique = 1, 'false', 'true'), '}'))))
) indexes,
(SELECT (@tbls:=NULL),
(SELECT (0) FROM information_schema.tables tbls
WHERE table_schema not in ('mysql','information_schema', 'sys', 'performance_schema')
AND (0x00) IN (@tbls:=CONCAT_WS(',', @tbls, CONCAT('{', '"schema":"', `TABLE_SCHEMA`, '",', '"table":"', `TABLE_NAME`, '",', '"rows":', `TABLE_ROWS`, ',', '"engine":"', `ENGINE`, '",', '"collation":"', `TABLE_COLLATION`, '"}'))))
tbls) x);
Please copy the output of the query from your SQL IDE here below (or upload it as a file).
Once you're done, please close this window and re-generate the optimization reports for your queries to retrieve the updated recommendations.
* The information you provide is encrypted and protected. Learn more on our security page.
Most of our advanced optimization algorithms require extra metadata from your database to effectively optimize this query (for example: the schema's structure, columns data distribution, etc.).
Read here how to quickly fetch this metadata using your favorite SQL IDE.
Once you got the metadata in a file, please upload the file here below:
* The information you provide is encrypted and protected. Learn more on our security page.
Hi, this is what you should know about your databases, in 30 seconds ...
-
To quickly boost your database performance in XX%, you should start with optimizing the top xx slow queries. Also, it seems something changed in your product this week, as we identified xx new slow queries worth optimizing. In addition, to improve performance and save on storage cost, we recommend to remove thesexx redundant indexes.
Optimize a single query
Analyze a slow query log
Ongoing performance analysis
Your SQL Queries Container
| Query Fingerprint | Total Duration | Executions | Status | Last Seen | Actions |
|---|
Best practices for optimal performance
Query View
Hi, please tell us which database you're using:
Amazon RDS
Amazon EC2
GCP Compute
GCP SQL
Azure VM
Azure DB
On-premises
Other
Bionic (v18.04)
Xenial (v16.04)
Trusty (v14.04)
Buster (v10)
Stretch (v9)
Jessie (v8)
Redhat
Ubuntu
Centos
Debian
Fedora
OpenSUSE
Windows
Other OS
|
||
|
||
|