by Markus Winand.

MySQL Example Scripts for “3-Minute Test”


This section contains the create, insert and select statements for the “Test your SQL Know-How in 3 Minutes” test. You may want to test yourself before reading this page.

The create and insert statements are available in the example schema archive.

The execution plans shown in this section are stripped to the relevant columns.

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2017
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2017-01-01'
   AND date_column <  DATE'2018-01-01'

The first execution plan performs a full index scan (type=index). The second execution plan, on the other hand, performs an index range scan (type=range). Note the the rows column also reflects the more efficient access method.

+-------+---------------+---------+------+--------------------------+
| type  | possible_keys | key     | rows | Extra                    |
+-------+---------------+---------+------+--------------------------+
| index | NULL          | tbl_idx |  300 | Using where; Using index |
+-------+---------------+---------+------+--------------------------+
+-------+---------------+---------+------+--------------------------+
| type  | possible_keys | key     | rows | Extra                    |
+-------+---------------+---------+------+--------------------------+
| range | tbl_idx       | tbl_idx |  271 | Using where; Using index |
+-------+---------------+---------+------+--------------------------+

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

The query uses access type REF, which is very similar to the RANGE access. However, the interesting part is that there is no SORT mentioned in the Extra column.

+------+---------------+---------+-------------+
| type | possible_keys | key     | Extra       |
+------+---------------+---------+-------------+
| ref  | tbl_idx       | tbl_idx | Using where | 
+------+---------------+---------+-------------+

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1
DROP INDEX tbl_idx ON tbl
CREATE INDEX tbl_idx ON tbl (b, a)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1

The first query can use both indexes efficiently (type=ref or range).

+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    1 | NULL  |
+------+---------------+---------+------+-------+
+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    1 | NULL  |
+------+---------------+---------+------+-------+

The second query cannot use the key to access the index—it reads the entire index (type=ALL). Changing the column order in the index allows both queries to use the REF (or RANGE) access method.

+------+---------------+------+------+-------------+
| type | possible_keys | key  | rows | Extra       |
+------+---------------+------+------+-------------+
| ALL  | NULL          | NULL |  300 | Using where |
+------+---------------+------+------+-------------+
+------+---------------+---------+------+-------+
| type | possible_keys | key     | rows | Extra |
+------+---------------+---------+------+-------+
| ref  | tbl_idx       | tbl_idx |    2 | NULL  |
+------+---------------+---------+------+-------+

Note that MySQL might also do a full index scan (type=Index). Although this might be better than a full table scan (depends on the data), it is far less efficient than a index range scan (type=ref or range).

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text)
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

The execution plan clearly states that it is doing an index range scan (type=range). Since there is only wild card character at the very end, the full search text 'TJ' can be used as index access predicate.

+-------+---------+---------+------+-----------------------+
| type  | key     | key_len | ref  | Extra                 |
+-------+---------+---------+------+-----------------------+
| range | tbl_idx | 258     | NULL | Using index condition |
+-------+---------+---------+------+-----------------------+

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Although the index is used efficiently in both queries (access type REF), the second query does not mention “Using index” in the extra column. That means the second query is not executed as index-only scan and will perform less efficient as the first.

+------+---------------+---------+--------------------------+
| type | possible_keys | key     | Extra                    |
+------+---------------+---------+--------------------------+
| ref  | tbl_idx       | tbl_idx | Using where; Using index |
+------+---------------+---------+--------------------------+
+------+---------------+---------+------------------------------------+
| type | possible_keys | key     | Extra                              |
+------+---------------+---------+------------------------------------+
| ref  | tbl_idx       | tbl_idx | Using index condition; Using where |
+------+---------------+---------+------------------------------------+

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license