EverSQL

Login
My Account Sensors Support
Logout

| Query Optimization Report

The optimal indexes for this query are ...

Copy

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:

Can't create the suggested temporary tables below? Click here to avoid them.

If the relationship between the tables isn't 1:1, the output from this optimized query may not match the original query's output. If that's the case, click here to retrieve another alternative.

The new optimized query is ...

Copy Diff

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 October 31)

Trusted by more than 45,000 engineers, including:

Starting at only $49 / month

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.

Trusted by more than 45,000 engineers, including:

Starting at only $149 / month

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.

Please wait ...

Or, sign up with your work email

Trusted by more than 40,000 engineers, including:

Please wait ...

Don't have an account? Register here Forgot your password? Click here

Already registered? Click to login. By registering, you agree to our Terms of Service and Privacy Policy.

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:

Drag & drop, or click to upload

* 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):

Drag & drop, or click to upload

* 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:

Redundant indexes report

Copy all indexes removal commands

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.

Visualize & optimize queries from slow query logs

Please submit the schema structure (How?):

Drag & drop, or click to upload

Please submit a MySQL or PostgreSQL slow query log (How?):

Drag & drop, or click to upload

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.

Drag & drop, or click to upload

* The information you provide is encrypted and protected. Learn more on our security page.

Drag & drop, or click to upload

* The information you provide is encrypted and protected. Learn more on our security page.

You're missing out! 60 seconds setup

Hi, did you know we can locate slow queries directly from your database? Start now.

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 in the last 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.