MySQL Top and Offset With Examples

A table can have a lot of records and selecting all the records can put a strain on the database performance. Instead, we can limit the number of records we are selecting by using the Top command.

The Top command selects N elements from the table. MySQL does not support the TOP command. However, it does support the LIMIT clause in its place.

MySQL Top syntax

The Basic syntax of the limit clause is 

Select <column-names>
FROM <table-name>
[where <conditions> ]
LIMIT [OFFSET] <number of records>;

The OFFSET option is optional. If the OFFSET is not specified, then the records are counted from the first record in the dataset.

LIMIT Clause without Offset in MySQL

We can skip the OFFSET clause completely and just mention the number of records wanted.

select * from film LIMIT 10;

LIMIT Clause with the offset in MySQL

When we mention an offset, the records are returned from that record number onwards till the number of records. This is especially useful for pagination queries.

select * from film LIMIT 10,20;

The above query returns records from 11 to 20.