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

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
36
views

We want to buy the book but I can't

Jul 18 at 21:36 Markus Winand ♦♦ 541
book
0
votes
2
answers
106
views
0
votes
0
answers
733
views

Performance very bad in Postgresql 9.3

Jul 08 at 11:54 Markus Winand ♦♦ 541
performance issue