In the session titled “Improving Performance with Better Indexes” by Ronald Bradford from EffectiveMySQL went over a test case he had with a customer to improve the performance of an already indexed system.  Basically we learned how to use the USING INDEX information in the explain plan.  He has a 6 step process:

• Capture
• Identify
• Confirm
• Analyze
• Optimize
• Verify

Performance Schema provides 3 tables events_statements_…  you can use to identify queries.  Ronald also went over several other ways to log SQL Statements.  Then he discussed how to identify the queries to look at.  Slowest query is not the best place to start.  Instead you need to look at total duration for all queries with the same signature in a given time window.  This is the same issue and common mistake made by DBAs on all platforms.  He then went over the following commands:

• EXPLAIN – The explain plan
• SHOW CREATE TALBE – shows DDL to create a table
• SHOW INDEXES FROM – shows statistics (cardinality only no histogram)
• INFORMATION_SCHEMA.TABLES – shows size of data on disk
• SHOW TABLE STATUS LIKE – also shows size of table on disk
• EXPLAIN EXTENDED – adds more information to explain plan

Talked about optimized trace https://dev/mysql.com/doc/internals/en/optimizer-tracing.html very briefly.

When looking at explain key: NULL means it is not using an index.  row: tells you the number or rows compared.  Also, adds Using Index to the Extra:.  This means that we are using a covering index and are not having to go to the table to get information from the rows.  key_len tells us the number of bytes being used for comparison, so bigger the better in query tuning.  Covering indexes can be an issue when large columns are involved.  If it is too big then you will add I/O to use your indexes.  If they are small enough then you will be entirely in memory.

This session had some great information.  I will probably take it back and combine it with my SQL Server 12 step query tuning session to make a MySQL tuning 12 step presentation to give to our developers.