Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Retrieve a large select by chunks in MySQL?
To retrieve large select by chunks, you need to use ORDER BY LIMIT. The syntax is as follows:
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 0,10;
From the above syntax, you will get 10 rows from the table. In the above syntax, 0 represents the first row from the result set of a table that means it is zero index based. The second value of LIMIT represents the maximum number of rows that can be retrieved from the table.
If you want the next rows after 10 to 30, then use in LIMIT like this. The syntax is as follows:
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 10,20; //11 to 30
If you want another set of rows from 30 to 50 then again use LIMIT:
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 30 ,20; 31 to 50.
You need to use temporary table for this. The syntax is as follows:
DROP TEMPORARY TABLE IF EXISTS yourTemporaryTableName; CREATE TEMPORARY TABLE yourTempTableName AS ( SELECT *FROM yourOriginalTableName ORDER BY LIMIT 0,100 );
If all records are in temporary table then get back all records from the temporary table using LIMIT as discussed above.
SELECT *FROM yourTemporaryTableName LIMIT 0,100; SELECT *FROM yourTemporaryTableName LIMIT 100,1000;
Now, it is up to you to set the limit value. It is a good practice now to DROP temporary table. The query is as follows:
DROP TEMPORARY TABLE yourTemporaryTableName;
Let us take a demo of the above discussion. First create a table. The query to create a table is as follows:
mysql> create table getRecordsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.68 sec)
Insert some records in the table using insert command. The query is as follows:
mysql> insert into getRecordsDemo values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); Query OK, 738 rows affected (0.34 sec) Records: 738 Duplicates: 0 Warnings: 0
Now create a temporary table like the above table. The query to create a temporary table is as follows:
mysql> drop temporary table if exists TempRecord; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create temporary table TempRecord -> as -> ( -> select * from getRecordsDemo order by Id limit 0,738 -> ); Query OK, 738 rows affected (0.03 sec) Records: 738 Duplicates: 0 Warnings: 0
Now you can get the result in chunks with LIMIT clause.
Case 1: The query is as follows to get some records from temporary table ‘TempRecord’:
mysql> select *from TempRecord limit 0,10;
The following is the output:
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+ 10 rows in set (0.00 sec)
Case 2: The query is as follows to get next set of records:
mysql> select *from TempRecord limit 10,20; +----+ | Id | +----+ | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | +----+ 20 rows in set (0.00 sec)
Case 3: The query is as follows to get another set of records:
mysql> select *from TempRecord limit 30,20; +----+ | Id | +----+ | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | +----+ 20 rows in set (0.00 sec)
3K+ Views
