MySQL – Limit Number of Rows
Does your MySQL table has large number of records and SELECT statements are fetching many records? Well you can limit the number of records a SELECT query can fetch from the database. This helps to reduce the load on the server. We shall go in detail about the advantages of LIMIT statement in MySQL, but firstly, in this MySQL Tutorial, let us see how to limit the number of records.
To limit the number of rows/records a SELECT query can fetch append your SQL Query with LIMIT modifier and the syntax of the SQL Query is given below:
your_sql_query LIMIT number_of_records;If the number of records in the database for the query is less than the specified, then all those records would be fetched.
LIMIT acts as an upper limit for the number of records.
Examples
Following are some of the examples for limiting number of records in MySQL
SELECT * FROM students LIMIT 2;
SELECT * FROM students WHERE section='B' LIMIT 2;Example 1 – LIMIT records in MySQL
Consider the following students table. It has four records in total.
Now we shall limit the number of records fetched by SELECT query to 2. Following is the SQL Query:
SELECT * FROM students LIMIT 2;
Where to use SQL LIMIT
Consider that you have an application which presents the user the list of items populated from the records of database table. Now it is evident that the user cannot see all the items at a time because of the limitation of the display unit size. When the user performs an action for more number of records like scrolling or clicking on pagination, you can do one more query to fetch the next limited number of records and present it to the user.
This above said scenario fits well for most of the web and mobile applications. You can use LIMIT to benefit from database response time, database load performance, network overhead time, etc.