MySQL optimization refresher notes

reference: http://www.databasejournal.com/features/mysql/article.php/10897_1382791_1/Optimizing-MySQL-Queries-and-Indexes.htm

Notes from above reference:

  • place EXPLAIN before your SELECT and learn a lot!
  • index index index…
  • in query, if possible, leave indexed field (usually after WHERE…) alone, or else every record must be calculated prior to conditional analysis…
  • ANALYZE TABLE tablename;
  • OPTIMIZE TABLE tablename
[quote block]

EXPLAIN SELECT employee_number,firstname,surname
FROM employee
WHERE employee_number= '10875';
+--------+----+-------------+----+-------+----+---+---------+
|table   |type|possible_keys|key |key_len|ref |rows| Extra  |
+--------+----+-------------+----+-------+----+---+---------+
|employee|ALL |NULL         |NULL|   NULL|NULL|  2|whereused|
+--------+----+-------------+----+-------+----+---+---------+

So what are all these things?

  • table shows us which table the output is about (for when you join many tables in the query)
  • type is an important one – it tells us which type of join is being used. From best to worst the types are: system, const, eq_ref, ref, range, index, all
  • possible_keys Shows which possible indexes apply to this table
  • key And which one is actually used
  • key_len give us the length of the key used. The shorter that better.
  • ref Tells us which column, or a constant, is used
  • rows Number of rows mysql believes it must examine to get the data
  • extra Extra info – the bad ones to see here are “using temporary” and “using filesort”

[quote block end]

Leave a Reply

You must be logged in to post a comment.