MySQL Explain & SQL_NO_CACHE

So today I was helping someone out with their database performance and ran across some poorly written queries. Now, certainly everyone makes mistakes, the goal is to do everything you can to avoid them.

So just a couple of helpful hints to do before you let a query loose into your environment.

Always execute the query via explain first. Something as simple as doing explain first will confirm no typos (since it will not run), and allow you optimize the query.
Several links already exist about how to use explain:

• http://ronaldbradford.com/blog/tag/explain/
• http://www.mysqlperformanceblog.com/2014/02/03/percona-toolkit-collection-pt-visual-explain/
• http://www.ustream.tv/recorded/15872720
• https://www.youtube.com/watch?v=qaB85pMVNJU
• https://dev.mysql.com/doc/refman/5.6/en/using-explain.html
• http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

The goal is simple. You want valid Keys in the possible_keys and keys (not null) and when it comes to key_len you do not want every table to be 100s of rows. If you can get the first key_len to be 200 (just a number I picked as an example) then the following to be 5,4,3,2,1 and not another 200 then your query should run well. That is a very simplistic and high level statement and I suggest you review the hyperlinks listed to understand Explain more. The query I saw today had 5+ joins and a sub-select (via a join was better in the where statement) and 200+ rows for every key_len. A few adjustments can allow your query to drop from 200 seconds to 1 second or less. Always, always, always do explain.

The next hint, try to test your queries with SQL_NO_CACHE. This allows you to test the real query and optimize it the best way you can. Once it becomes cached ( if it will be ) then it will just run that much faster for you. 

• https://dev.mysql.com/doc/refman/5.6/en/select.html
• http://ronaldbradford.com/blog/using-the-mysql-query-cache-effectively-2009-09-28/

One last note... Take a look at the SQL Performance tips for MySQL that once existed on the forge site but now are at - https://wikis.oracle.com/pages/viewpage.action?pageId=27263381