Basic Indexing
Show indexes on a table
Use the SHOW INDEX and include the table name and database name.
mysql> show index from city from sakila;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | city_id | A | 600 | NULL | NULL | | BTREE | | |
| city | 1 | idx_fk_country_id | 1 | country_id | A | 109 | NULL | NULL | | BTREE | | |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Or alternatively only use the SHOW INDEX FROM <TABLE NAME>
mysql> show index from city;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| city | 0 | PRIMARY | 1 | city_id | A | 600 | NULL | NULL | | BTREE | | |
| city | 1 | idx_fk_country_id | 1 | country_id | A | 109 | NULL | NULL | | BTREE | | |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Use INFORMATION_SCHEMA.STATISTICS to show indexes
The INFORMATION_SCHEMA.STATISTICS contains index information for all tables;
mysql> SELECT INDEX_TYPE, NON_UNIQUE, INDEX_NAME, COLUMN_NAME, CARDINALITY
-> FROM INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_NAME = 'city';
+------------+------------+-------------------+-------------+-------------+
| INDEX_TYPE | NON_UNIQUE | INDEX_NAME | COLUMN_NAME | CARDINALITY |
+------------+------------+-------------------+-------------+-------------+
| BTREE | 0 | PRIMARY | city_id | 600 |
| BTREE | 1 | idx_fk_country_id | country_id | 109 |
+------------+------------+-------------------+-------------+-------------+
Index Usage in the WHEERE clause
Lets run the query on the film table and find all films that have a length of 100.
mysql> SELECT film_id, length
-> FROM film
-> WHERE length = 100;
+---------+--------+
| film_id | length |
+---------+--------+
| 65 | 100 |
| 71 | 100 |
| 221 | 100 |
| 236 | 100 |
| 322 | 100 |
| 387 | 100 |
| 399 | 100 |
| 445 | 100 |
| 681 | 100 |
| 703 | 100 |
| 979 | 100 |
| 994 | 100 |
+---------+--------+
Use EXPLAIN to analyze the query
We can use the EXPLAIN keyword to see an estimation of how the optimizer would run the query
mysql> explain SELECT film_id, length FROM film WHERE length = 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
You can notive no indexes are being used for this query, in the possible_keys column there's no index that can satisfy this query.
We can also enable profiling and see how the query was run until we got the result sback.
Enable Profiling
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Notice the warning. Oracle recommends to use the performance schema instead.
Run the query again and now use the
SHOW PROFILES statements to see the gathered shows.
mysql> SELECT film_id, length FROM film WHERE length = 100;
Show Gathered Profiles
You can use the SHOW PROFILES statement to show gathered profiles.
mysql> show profiles;
+----------+------------+-----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------+
| 1 | 0.00013000 | SELECT film_id, length FROM film WHERE length = 100 |
+----------+------------+-----------------------------------------------------+
Display Profiling Information For a Query
We can display the profiling infromation for a specific query using its query id
mysql> show profile for query 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000106 |
| Waiting for query cache lock | 0.000026 |
| starting | 0.000005 |
| checking query cache for query | 0.000016 |
| checking privileges on cached | 0.000006 |
| checking permissions | 0.000017 |
| sending cached result to clien | 0.000024 |
| cleaning up | 0.000009 |
+--------------------------------+----------+
We can clearly see the steps used to run this query.
Percona Toolkit pt-visual-explain
Another too we could have used is from Percona. The pt-visual-explain takes the output of the EXPLAIN statement and visualizes it.
Install pt-visual-explain
joseph@ubuntu:~$ sudo apt-get install percona-toolkit
Get Visual Tree from Query
pt-visual-explain can use the file or take the result of the 'EXPLAIN'
joseph@ubuntu:~$ mysql -e "explain select film_id, length from sakila.film where length = 100" | pt-visual-explain
Filter with WHERE
+- Table scan
rows 1000
+- Table
table film
We can notice that we are doing an index scan on the table and touching 1000 row.
Create an index
Lets create an index on the length column.
mysql> CREATE INDEX ix_film_length ON film (length);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
and lets re-run our query with EXPLAIN
mysql> explain SELECT film_id, length FROM film WHERE length = 100;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | ref | ix_film_length | ix_film_length | 3 | const | 12 | 100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
Notice now we are using an index on the possible_keys. The index name is ix_film_length our newly created index. Also the extra column shows that we are now using an index.
Lets clean up and drop the index
mysql> drop index ix_film_length on film;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0