indexes an introduction

1 . Indexes - What You Need to Know http://www.percona.com/training/ © 2011 - 2017 Percona, Inc. 1 / 53 

2 . Indexes - Need to Know QUERY PLANNING © 2011 - 2017 Percona, Inc. 2 / 53 

3 . About This Chapter The number one goal is to have faster queries. The process is: We first ask MySQL what its intended execution plan is. If we don't like it, we make a change, and try again... © 2011 - 2017 Percona, Inc. 3 / 53 

4 . It All Starts with EXPLAIN Bookmark this manual page: http://dev.mysql.com/doc/refman/5.7/en/explain- output.html It is the best source for anyone getting started. © 2011 - 2017 Percona, Inc. 4 / 53 

5 . Example Data IMDB database loaded into InnoDB tables (~5GB) Download it and import it for yourself using imdbpy2sql.py: http://imdbpy.sourceforge.net © 2011 - 2017 Percona, Inc. 5 / 53 

6 . Table of Interest CREATE TABLE title ( id int NOT NULL AUTO_INCREMENT, title text NOT NULL, imdb_index varchar(12) DEFAULT NULL, kind_id int NOT NULL, production_year int DEFAULT NULL, imdb_id int DEFAULT NULL, phonetic_code varchar(5) DEFAULT NULL, episode_of_id int DEFAULT NULL, season_nr int DEFAULT NULL, episode_nr int DEFAULT NULL, series_years varchar(49) DEFAULT NULL, md5sum varchar(32) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; © 2011 - 2017 Percona, Inc. 6 / 53 

7 . Find the Title Bambi mysql> EXPLAIN SELECT id,title,production_year FROM title -> WHERE title = 'Bambi' ORDER BY production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3331824 filtered: 10.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 7 / 53 

8 . Warning on EXPLAIN? mysql> show warnings\G ************* 1. row ************* Level: Note Code: 1003 Message: /* select#1 */ select `imdb`.`title`.`id` AS `id`, `imdb`.`title`.`title` AS `title`, `imdb`.`title`.`production_year` AS `production_year` from `imdb`.`title` where (`imdb`.`title`.`title` = 'Bambi') order by `imdb`.`title`.`production_year` 1 row in set (0.00 sec) Displays how the optimizer qualifies table and column names in the SELECT statement What the query looks like after rewriting and optimization rules are applied © 2011 - 2017 Percona, Inc. 8 / 53 

9 . Aha! Now Add an Index mysql> ALTER TABLE title ADD INDEX (title); ERROR 1170 (42000): BLOB/TEXT column 'title' used in key specification without a key length © 2011 - 2017 Percona, Inc. 9 / 53 

10 . Aha! Now Add an Index mysql> ALTER TABLE title ADD INDEX (title); ERROR 1170 (42000): BLOB/TEXT column 'title' used in key specification without a key length mysql> ALTER TABLE title ADD INDEX (title(50)); Query OK, 0 rows affected (8.09 sec) Records: 0 Duplicates: 0 Warnings: 0 © 2011 - 2017 Percona, Inc. 10 / 53 

11 . Let's Revisit mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title = 'Bambi' ORDER by production_year\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ref possible_keys: title key: title key_len: 152 ref: const rows: 4 filtered: 100.00 Extra: Using where; Using filesort 1 row in set, 1 warning (0.00 sec) ref is equality for comparison, but not PK lookup. Identified 'title' as a candidate index and chose it. Size of the index used. Anticipated number of rows. © 2011 - 2017 Percona, Inc. 11 / 53 

12 . Other Ways of Accessing mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE id = 55327\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) const: at most, one matching row. Primary Key in InnoDB is always faster than secondary keys. © 2011 - 2017 Percona, Inc. 12 / 53 

13 . LIKE mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title LIKE 'Bamb%'\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: range possible_keys: title key: title key_len: 152 ref: NULL rows: 176 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) Type is Range. BETWEEN, IN() and < > are also ranges. Number of rows to examine has increased; we are not specific enough. © 2011 - 2017 Percona, Inc. 13 / 53 

14 . Why is That a Range? We're looking for titles between BambA and BambZ* When we say index in MySQL, we mean trees. That is, B-Tree/B+Tree/T-Tree. Pretend they're all the same (for simplification). There is only radically different indexing methods for specialized uses: MEMORY Hash, FULLTEXT, spatial or 3rd party engines. © 2011 - 2017 Percona, Inc. 14 / 53 

15 . What's That? © 2011 - 2017 Percona, Inc. 15 / 53 

16 . Could This Be a Range? mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title LIKE '%ulp Fiction'\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3331824 filtered: 11.11 Extra: Using where 1 row in set (0.00 sec) © 2011 - 2017 Percona, Inc. 16 / 53 

17 . No, We Can't Traverse © 2011 - 2017 Percona, Inc. 17 / 53 

18 . LIKE 'Z%' mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title LIKE 'Z%'\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: range possible_keys: title key: title key_len: 152 ref: NULL rows: 24934 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 18 / 53 

19 . LIKE 'T%' mysql> EXPLAIN SELECT id, title, production_year FROM title -> WHERE title LIKE 'T%'\G ************* 1. row ************* id: 1 select_type: SIMPLE table: title type: ALL possible_keys: title key: NULL key_len: NULL ref: NULL rows: 3331824 filtered: 21.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) © 2011 - 2017 Percona, Inc. 19 / 53 

20 . MySQL is Reasonably Smart It dynamically samples the data to choose which is the better choice—or in some cases uses static statistics. This helps the optimizer choose: Which indexes will be useful. Which indexes should be avoided. Which is the better index when there is more than one. © 2011 - 2017 Percona, Inc. 20 / 53 

21 . Why Avoid Indexes? B-Trees work like humans search a phone book; Use an index if you want just a few rows. Scan cover-to-cover if you want a large percentage. © 2011 - 2017 Percona, Inc. 21 / 53 

22 . Why Avoid Indexes (cont.) Benchmark on a different schema (lower is better): © 2011 - 2017 Percona, Inc. 22 / 53 

23 . What You Should Take Away Data is absolutely critical. Development environments should contain sample data exported from production systems. A few thousands of rows is usually enough for the optimizer to behave like it does in production. © 2011 - 2017 Percona, Inc. 23 / 53 

24 . What You Should Take Away (cont.) Input values are absolutely critical. Between two seemingly identical queries, execution plans may be very different. Just like you test application code functions with several values for input arguments. © 2011 - 2017 Percona, Inc. 24 / 53 

25 . Indexes - Need to Know EXPLAINING THE EXPLAIN © 2011 - 2017 Percona, Inc. 25 / 53 

26 . How to Explain the EXPLAIN In queries with regular joins, tables are read in the order displayed by EXPLAIN. id is a sequential identifier of SELECT statements in the query. select_type indicates type of SELECT (simple, primary, subquery, union, derived, ...). type says which join type will be used. possible_keys indicates which indexes MySQL can choose from to find the rows in this table. key indicates which index is used. partitions shows which partitions are being accessed. © 2011 - 2017 Percona, Inc. 26 / 53 

27 . How to Explain the EXPLAIN (cont.) key_len longest length of the key that was used (which parts of a composite index are being used). (http://bugs.mysql.com/bug.php?id=83062) ref which columns or constants are compared to the index to select rows from the table. filtered shows the estimated percentage of table rows that will be filtered by the table condition. rows says how many rows have to be examined in order to execute each step of the query. Extra contains additional information about how MySQL resolves the query http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information © 2011 - 2017 Percona, Inc. 27 / 53 

28 . Types in EXPLAIN The following slides show possible values for EXPLAIN type, ordered (approximately) from the fastest to the slowest. FULLTEXT access type (and its special indexes) are not covered on this section. © 2011 - 2017 Percona, Inc. 28 / 53 

29 . NULL Not really a plan: no data is returned See 'Extra' for a reason mysql> EXPLAIN SELECT * FROM title WHERE 1 = 2\G ************* 1. row ************* select_type: SIMPLE table: NULL type: NULL possible_keys: NULL key: NULL -- Internally equivalent to key_len: NULL -- SELECT NULL WHERE 0; ref: NULL rows: NULL filtered: NULL Extra: Impossible WHERE 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM title WHERE id = -1\G ... type: NULL Extra: no matching row in const table © 2011 - 2017 Percona, Inc. 29 / 53