Index key1 col1 not needed use prefix indexes on

– index key1 (col1) <-- Not needed!
 Use prefix indexes on large keys
 Best indexes are 16 bytes/chars or less
 Indexes bigger than 32 bytes/chars should be looked
at very closely
– should have there own cache if in MyISAM
 For large strings that need to be indexed, i.e. URLs,
consider using a separate column using the MySQL
MD5 to create a hash key and index on it instead

The World’s Most Popular Open Source Database Copyright 2010 Oracle
Explain
explain select C.Name, Y.Name, Y.Population, Language from Country as C, City as Y, CountryLanguage as L where Y.Name = C.Name and L.CountryCode
= Y.CountryCode and C.Name = 'Macao' ;
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------------+
| 1 | SIMPLE | C | ALL | NULL | NULL | NULL | NULL | 239 | Using where |
| 1 | SIMPLE | Y | ALL | NULL | NULL | NULL | NULL | 4079 | Using where; Using join buffer |
| 1 | SIMPLE | L | ref | PRIMARY | PRIMARY | 3 | world.Y.CountryCode | 9 | Using index |
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------------+
3 rows in set (0.00 sec)
(Using the MySQL World database)
• Order that the tables are accessed
• Indexes used
• Estimated number of rows accessed per table
• select C.Name, Y.Name, Y.Population, Language from
Country as C, City as Y, CountryLanguage as L where
Y.Name = C.Name and L.CountryCode = Y.CountryCode and
C.Name = 'Macao' ;

The World’s Most Popular Open Source Database Copyright 2010 Oracle
Explain - Details
• Tables are accessed from top to bottom
• Columns
• Select Type - SELECT if no Union or Subquery
• Table, uses aliases
• Type - Most common ref or eq_ref
• Possible Keys - Indexes the optimizer is considering
• Key = The index the optimizer chose
• Ref - What column in what table (using alias) is referenced by
the index
• Rows - Estimated number of rows per reference
• Multiple these to get overall cost
• There are more values, see:

The World’s Most Popular Open Source Database Copyright 2010 Oracle
More Explain
• alter table Country add index c2 (Name) ;
• alter table City add index c2 (Name) ;
mysql> explain select C.Name, Y.Name, Y.Population, Language from Country as C, City as Y, CountryLanguage as L where Y.Name =
C.Name and L.CountryCode = Y.CountryCode and C.Name = 'Macao' ;
+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | C | ref | c2 | c2 | 52 | const | 1 | Using where; Using index |
| 1 | SIMPLE | Y | ref | c2 | c2 | 35 | const | 1 | Using where |
| 1 | SIMPLE | L | ref | PRIMARY | PRIMARY | 3 | world.Y.CountryCode | 9 | Using index
|+----+-------------+-------+------+---------------+---------+---------+---------------------+------+--------------------------+
3 rows in set (0.00 sec)
• The original cost was 239 * 4079 * 9 = 8,773,929
• The new cost is 1 * 1 * 9 = 9

Query Tuning Rules

The World’s Most Popular Open Source Database Copyright 2010 Oracle
Queries I
 Often the # 1 issue in overall performance
 Always, Always have your slow query log on !
– Use: log_queries_not_using_indexes
– Check it regularly
– Use mysqldumpslow :
mysqldumpslow.html
– Best practice is to automate running mysqldumpslow every
morning and email results to DBA, DBDev, etc.