MySQL Limit Data

In this tutorial we can see how to retreive records in table and limit the number of records returned based on a limit value.

The limit clause in Mysql is used to restrict the number of rows retrieved in the result set of the query to a certain count. It also helps us to retrieve the particular number of the records beginning from a particular offset from the MySQL table. Whenever the tables contain a huge amount of records, it becomes a heavy operation and even time-consuming to retrieve all these records and display them.

Suppose we have a records of "student table"select all records a table .example below

then we wish to show records 1-3 from a table then mysql query syntax is :

Select * from table_name

LIMIT number;

now we want to show records 1-3 from student table

then query is :

Select * from student_table

LIMIT 3;

Using the OFF SET in the LIMIT query

The OFF SET value is also most often used together with the LIMIT keyword. The OFF SET value allows us to specify which row to start from retrieving data

Let’s suppose that we want to get a limited number of members starting from the middle of the rows, we can use the LIMIT keyword together with the offset value to achieve that. The script shown below gets data starting the fourth row and limits the results to 3.

Select * from student_table

LIMIT 3,3 ;

Output :

When should we use the LIMIT keyword?

Let’s suppose that we are developing the application that runs on top of myflixdb. Our system designer have asked us to limit the number of records displayed on a page to say 20 records per page to counter slow load times. How do we go about implementing the system that meets such user requirements? The LIMIT keyword comes in handy in such situations. We would be able to limit the results returned from a query to 20 records only per page.

Conclusion

We can restrict the number of records to be retrieved from the result set by using the LIMIT clause in MySQL. This clause also helps us to specify the offset from where the row counting should start while retrieval and specify the row count which tells how many rows are to be retrieved.