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

[email protected]:~$ 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'

[email protected]:~$ 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

results matching ""

    No results matching ""