TOP X of SQL Server in MySQL Analog

Question

I am looking for SQL Server TOP (X) Analog in MySQL

In SQL Server:

SELECT
  TOP 5
  Z
...
FROM
ORDER BY Z DESC

How can this be done in MySQL?

ADDED:

I have found for other RDBMS: Returning TOP N Records

Answer 1

SELECT
...
FROM
ORDER BY
LIMIT 5 [OFFSET 10] --Use offset for paging.

Reference: http://dev.mysql.com/doc/refman/5.1/en/select.html

Answer 2

Before you attempt to do any TOP X queries in MySQL, you must make sure that indexes are in place for the retrieval of that type.

Here is why: MySQL uses handlers to traverse tables and indexes using more direct I/O operations. The stats for handlers movement and data retrieval are logged by the following status variables:

Here is the complete list of Handler status variables in MySQL in 5.1

• Handler_commit :The number of internal COMMIT statements. This variable was added in MySQL 4.0.2.
• Handler_delete : The number of times a row was deleted from a table.
• Handler_prepare : A counter for the prepare phase of two-phase commit operations
• Handler_read_first : The number of times the first entry in an index was read. If this value is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed.
• Handler_read_key : The number of requests to read a row based on a key. If this value is high, it is a good indication that your tables are properly indexed for your queries.
• Handler_read_next : The number of requests to read the next row in key order. This value is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
• Handler_read_prev : The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC. This variable was added in MySQL 3.23.6.
• Handler_read_rnd : The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.
• Handler_read_rnd_next : The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
• Handler_rollback : The number of requests for a storage engine to perform a rollback operation.
• Handler_savepoint : The number of requests for a storage engine to place a savepoint.
• Handler_savepoint_rollback : The number of requests for a storage engine to roll back to a savepoint.
• Handler_update : The number of requests to update a row in a table.
• Handler_write : The number of requests to insert a row in a table.

For example, if you ran this query:

SELECT post_id,post_date,post_text FROM articles;

and you want the top 40 articles by date:

SELECT post_id,post_date,post_text FROM articles ORDER BY post_date DESC LIMIT 40;

there will definitely be some handler navigation through the table and index, especially since MySQL 5.1 does not have a handler_read_last (That's MySQL 5.5)

The smart thing to do is to refactor such a query to guarantee as little key data being need as possible to gather data requested. Hence, the new query should appear as follows:

SELECT A.post_id,A.post_date,A.post_text
FROM articles A INNER JOIN 
(SELECT post_id FROM articles
ORDER BY post_date DESC LIMIT 40) B
USING (post_id);

Notice that the limit is imposed on the keys only. To make sure the inline query reads only from the index, create an index like this:

ALTER TABLE articles ADd INEX (post_date,post_id);

As the amount of data grows, the running time of this query should remain constant. I tested this theory out in StackOverflow where I answered a question about this kind of data retrieval getting the 40 most recent article posts. This same paradigm must be applied to doing TOP X queries in MySQL.