Limiting Query Results in MySQL

When using MySQl databases, a SQL clause that can be used in queries is LIMIT. In a SELECT query, WHERE dictates which records to return, and ORDER BY decides how those records are sorted. The LIMIT clause states how many records to return. It is used like so:

SELECT * FROM tablename LIMIT x;
In this case, only the initial x records are returned from the query. So to return 5 records, you would write the query SELECT * FROM tablename LIMIT 5;

Using the format below, you can have y records returned, starting at x.

SELECT * FROM tablename LIMIT x,y;
SELECT * FROM tabename LIMIT 10,10;

This statement would return records 11 through 20. This is because like arrays, result sets begin at 0 when it comes to LIMIT, so 10 is the 11th record. To return the last records in a data set, you would write the query as follows:

SELECT * FROM tablename LIMIT 20,1;
Here the 20th record is returned.

The LIMIT x,y clause is used most frequently when paginating query results(showing them in blocks over multiple pages). However, the LIMIT clause does not improve query processing speed as the SQL engine still has to assemble the entire result set and then truncate the list. In addition, the LIMIT clause is not part of the SQL standard and is therefore not available in all databases.

Nonetheless, in MySQL, you can use LIMIT with most types of queries, not just with SELECT statements. You can also use the LIMIT with WHERE and/or ORDER BY clauses, but LIMIT must always come last in the query.