2011-08-13MySQL Example Scripts for “3-Minuten 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 execution plans shown in this section are stripped to the relevant columns.

Table Setup

The GENERATOR_X views are explained in the MySQL Row Generators article.

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;


INSERT INTO tbl
SELECT gen.n
     , DATE_SUB(CURDATE(), INTERVAL gen.n DAY)
     , MOD(gen.n,1234)
     , FLOOR(RAND()*10)
     , GROUP_CONCAT(CHAR((RAND() * 25)+97) SEPARATOR '')
     , IF(MOD(gen.n, 5) = 0, 'X', 'A')
  FROM generator_64k gen
     , generator_16 rnd
 WHERE gen.n < 50000
 GROUP BY gen.n;

ANALYZE TABLE tbl;

Question 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);


EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE YEAR(date_column) = '2011';

EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE date_column >= STR_TO_DATE('2011-01-01', '%Y-%m-%d')
    AND date_column <  STR_TO_DATE('2012-01-01', '%Y-%m-%d');


ALTER TABLE tbl DROP INDEX tbl_idx;

The first execution plan performs a full table scan. 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       |
+------+---------------+------+-------+-------------+
| ALL  | NULL          | NULL | 50212 | Using where | 
+------+---------------+------+-------+-------------+


+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     | rows  | Extra            |
+-------+---------------+---------+-------+------------------+
| range | tbl_idx       | tbl_idx |   206 | [..] Using index | 
+-------+---------------+---------+-------+------------------+

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

EXPLAIN
 SELECT id, date_column
   FROM tbl
  WHERE a = 123
  ORDER BY date_column DESC
  LIMIT 1;


ALTER TABLE tbl DROP INDEX tbl_idx;

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; Using index | 
+------+---------------+---------+--------------------------+

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b);

EXPLAIN
 SELECT id, a, b
   FROM tbl
  WHERE a = 123
    AND b = 1;

EXPLAIN
 SELECT id, a, b
   FROM tbl
  WHERE b = 123;

ALTER TABLE tbl DROP INDEX tbl_idx;

The second query cannot use the key to access the index—it reads the entire index (type=Index). The signs are the same as for question 1 above: no possible key, still using the index, but reading all rows from it. Changing the column order in the index allows both queries to use the REF access method.

+------+---------------+---------+------+------------------+
| type | possible_keys | key     | rows | Extra            |
+------+---------------+---------+------+------------------+
| ref  | tbl_idx       | tbl_idx |    5 | [..] Using index |
+------+---------------+---------+------+------------------+


+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     |  rows | Extra            |
+-------+---------------+---------+-------+------------------+
| index | NULL          | tbl_idx | 50304 | [..] Using index |
+-------+---------------+---------+-------+------------------+

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);

EXPLAIN
 SELECT id, text
   FROM tbl
  WHERE text LIKE '%TERM%';

ALTER TABLE tbl DROP INDEX tbl_idx;

The explain plan looks OK on the first sight because it says  “Using index”. Access type INDEX looks also promising. However, you should become doubtful if you see that an index is used although no possible keys are listed. That means that the entire index is read.

+-------+---------------+---------+-------+------------------+
| type  | possible_keys | key     |  rows | Extra            |
+-------+---------------+---------+-------+------------------+
| index | NULL          | tbl_idx | 50238 | [..] Using index |
+-------+---------------+---------+-------+------------------+

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column);

EXPLAIN
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
  GROUP BY date_column;

EXPLAIN
 SELECT date_column, count(*)
   FROM tbl
  WHERE a = 123
    AND b = 1
 GROUP BY date_column;

ALTER TABLE tbl DROP INDEX tbl_idx;

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 where |
+------+---------------+---------+-------------+