EverSQL

Login
My Account Sensors Support
Logout

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

Trusted by more than 40,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 40,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 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 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": [',@cols,'], "indexes": [',@indexes,'], "tables":[',@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, '}'))))
    ) 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 detected

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
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": [',@cols,'], "indexes": [',@indexes,'], "tables":[',@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, '}'))))
    ) 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.

Optimized Queries

How many queries you optimized

Pending Queries

Added, but not yet optimized

Slow Queries

Queries with avg. duration >= 1sec

Avg. Duration

0 sec

How slow were your queries?

Your Personal Database Performance Insights

Please note:

Your SQL Queries Container

This is your query container, in which you can manage, analyze and optimize your SQL queries.

Add Single Query Analyze Slow Query Log Update Database Metadata

Apply / Clear Filters
Filter by status:
- Optimized
- Pending
- Error
Filter by query content:

Clear filters

Clear all filters

Get Indexing Recommendations

	Query Fingerprint	Total Duration	Executions	Status	Last Seen	Actions

Please note:

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

We have a few alternatives available for optimized queries. If the query below returns unexpected results, click here to retrieve another alternative.

To optimize this query, we recommend to ...

Create Optimal Indexes

Copy Commands Export

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:

Optimal indexing is crucial for query optimization. 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: