index key1 col1 Not needed Use prefix indexes on large keys Best indexes are 16

Index key1 col1 not needed use prefix indexes on

This preview shows page 26 - 32 out of 42 pages.

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
Background image
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 fromCountry as C, City as Y, CountryLanguage as L whereY.Name = C.Name and L.CountryCode = Y.CountryCode andC.Name = 'Macao' ;
Background image
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:
Background image
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
Background image
Query Tuning Rules
Background image
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.htmlBest practice is to automate running mysqldumpslowevery morning and email results to DBA, DBDev, etc.
Background image
Image of page 32

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture