How to check performance of mysql query?

Question

I have been learning query optimization, increase query performance and all but in general if we create a query how can we know if this is a wise query.

I know we can see the execution time below, But this time will not give a clear indication without a good amount of data. And usually, when we create a new query we don't have much data to check.

I have learned about clauses and commands performance. But is there is anything by which we can check the performance of the query? Performance here is not execution time, it means that whether a query is "ok" or not, without data dependency.

As we cannot create that much data that would be in live database.

Solution 1

General performance of a query can be checked using the EXPLAIN command in MySQL. See https://dev.mysql.com/doc/refman/5.7/en/using-explain.html

It shows you how MySQL engine plans to execute the query and allows you to do some basic sanity checks i.e. if the engine will use keys and indexes to execute the query, see how MySQL will execute the joins (i.e. if foreign keys aren't missing) and many more.

You can find some general tips about how to use EXPLAIN for optimizing queries here (along with some nice samples): http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Solution 2

As mentioned above, Right query is always data-dependent. Up to some level you can use the below methods to check the performance

1. You can use Explain to understand the Query Execution Plan and that may help you to correct some stuffs. For more info : Refer Documentation Optimizing Queries with EXPLAIN

2. You can use Query Analyzer. Refer MySQL Query Analyzer

Solution 3

I like to throw my cookbook at Newbies because they often do not understand how important INDEXes are, or don't know some of the subtleties.

When experimenting with multiple choices of query/schema, I like to use

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

That counts low level actions, such as "read next record". It essentially eliminates caching issues, disk speed, etc, and is very reproducible. Often there is a counter in that output (or multiple counters) that match the number of rows in the table (sometimes +/-1) -- that tells me there are table scan(s). This is usually not as good as if some INDEX were being used. If the query has a LIMIT, that value may show up in some Handler.

A really bad query, such as a CROSS JOIN, would show a value of N*M, where N and M are the row counts for the two tables.

I used the Handler technique to 'prove' that virtually all published "get me a random row" techniques require a table scan. Then I could experiment with small tables and Handlers to come up with a list of faster random routines.

Another tip when timing... Turn off the Query_cache (or use SELECT SQL_NO_CACHE).

Comments

[Anand Singh]

Explain i already know but Query Analyzer is very help full thanks :)