MySQL Example Scripts for “Sorting and Grouping”


This section contains the code and execution plans for Chapter 6 in a MySQL database.

Indexed Order By

ALTER TABLE sales
 DROP INDEX sales_date;

ALTER TABLE sales
  ADD INDEX sales_dt_pr (sale_date, product_id);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date, product_id;

There is no “Extra: Using filesort”:

Die Ausführung erfolgt ohne „Extra: Using filesort“:

+-------------+------+-------------+------+-------------+
| select_type | type | key         | rows | Extra       |
+-------------+------+-------------+------+-------------+
| SIMPLE      | ref  | sales_dt_pr |    1 | Using where |
+-------------+------+-------------+------+-------------+

The same execution plan is used when sorting for PRODUCT_ID only:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  ORDER BY product_id;

Using greater or equals requires an explicit sort:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY product_id;

Execution plan re-formatted for a better fit on the page:

+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

Order By ASC/DESC and NULLS FIRST/LAST

MySQL uses the index backwards, but doesn’t mention it in the execution plan:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date DESC, product_id DESC;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where | 
+-------------+-------+-------------+------+-------------+

Mixing ASC and DESC requires explicit sorting:

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id DESC;
+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

MySQL Accepts ASC and DESC specification in the index definition, but ignores it.

ALTER TABLE sales
 DROP INDEX sales_dt_pr;

ALTER TABLE sales
  ADD INDEX sales_dt_pr (sale_date ASC, product_id DESC);

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id DESC;
+-------------+------+-------------+------+----------------+
| select_type | type | key         | rows | Extra          |
+-------------+------+-------------+------+----------------+
| SIMPLE      | ref  | sales_dt_pr |  117 | Using where;   |
|             |      |             |      | Using filesort |
+-------------+------+-------------+------+----------------+

To proof that, it is still avoiding the sort when both columns are sorted ascending.

EXPLAIN
 SELECT sale_date, product_id, quantity
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  ORDER BY sale_date ASC, product_id ASC;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where | 
+-------------+-------+-------------+------+-------------+

Indexing Group By

An indexed group by show now sort operation in the execution plan:

EXPLAIN
 SELECT product_id, sum(eur_value)
   FROM sales
  WHERE sale_date = CURDATE() - INTERVAL 1 DAY
  GROUP BY product_id;
+-------------+-------+-------------+------+-------------+
| select_type | type  | key         | rows | Extra       |
+-------------+-------+-------------+------+-------------+
| SIMPLE      | ref   | sales_dt_pr |    1 | Using where | 
+-------------+-------+-------------+------+-------------+

A regular group by is executed with the sort/group algorithm, because MySQL does not implement Hash-Group as of release 5.6.

EXPLAIN
 SELECT product_id, sum(eur_value)
   FROM sales
  WHERE sale_date >= CURDATE() - INTERVAL 1 DAY
  GROUP BY product_id;
+-------------+-------+-------------+------+------------------+
| select_type | type  | key         | rows | Extra            |
+-------------+-------+-------------+------+------------------+
| SIMPLE      | range | sales_dt_pr |  117 | Using where;     |
|             |       |             |      | Using temporary; |
|             |       |             |      | Using filesort   |
+-------------+-------+-------------+------+------------------+

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

2
votes
1
answer
1.5k
views
0
votes
2
answers
867
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 771
oracle index update
1
vote
1
answer
299
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 771
testcase postgres