MySQL Example Scripts for “Partial Results”


This section contains the create and insert statements to run the examples from Chapter 7, “Partial Results” in a MySQL database.

Querying Top-N Rows

An indexed Top-N query doesn’t show a “filesort” operation in the Extras column:

SELECT *
  FROM sales
 ORDER BY sale_date DESC
 LIMIT 10;

+----+-------+-------+-------------+--------+-------+
| id | table | type  | key         | rows   | Extra |
+----+-------+-------+-------------+--------+-------+
|  1 | sales | index | sales_dt_pr | 836092 |       | 
+----+-------+-------+-------------+--------+-------+

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

0
votes
2
answers
731
views

different execution plans after failing over from primary to standby server

2 days ago Markus Winand ♦♦ 741
oracle index update
1
vote
1
answer
67
views

Generate test data for a given case

Sep 14 at 18:11 Markus Winand ♦♦ 741
testcase postgres
0
votes
1
answer
215
views

Database design suggestions for a data scraping/warehouse application?

Aug 27 at 09:29 Markus Winand ♦♦ 741
mysql optimization database