How to check performance of mysql query?

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).

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/

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