MySQL LIMIT

What is LIMIT and how it is useful?

• Limit is very useful for pagination and use Limit to build a pagination page
• Pagination is used in some form in almost every web application to divide returned data and display it on multiple pages Pagination.
• PHP and MySQL pagination example. PHP MySQL and Pagination

user2 table

CREATE TABLE user2(
user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
loginEmail VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL
);

Insert Record for testing

INSERT INTO user2 VALUES 
(1,'admin@w3cyberlearning','d4Z6Jm1L5KLY6'),
(2,'user@w3cyberlearning','d4yOXqnMb09iw'),
(3,'sophal@w3cyberlearning','d4elKQV.j6LcQ'),
(4,'yiling@w3cyberlearning','d4TBKG4h5SUkY'),
(5,'yiling1@w3cyberlearning','d4J/AM81hPu5.'),
(6,'kwan@w3cyberlearning','d4Z.cnO8IUAoA'),
(7,'kwan1@w3cyberlearning','d4NthJ11wK0KQ'),
(8,'kwan1@w3cyberlearning','d4NthJ11wK0KQ'),
(9,'kwan1@w3cyberlearning','d4NthJ11wK0KQ'),
(10,'wu@w3cyberlearning','d4yk8ErVmdF1w'),
(11,'wu@w3cyberlearning','d4yk8ErVmdF1w'),
(13,'wu@w3cyberlearning','d4yk8ErVmdF1w'),
(14,'[email protected]','d4D9O7zfTlRZE'),
(16,'[email protected]','82hngl1f0jsxA'),
(17,'[email protected]','82VgBddnErejI');

Select All record

mysql> select * from user2;
+---------+------------------------------+---------------+
| user_id | loginEmail                   | password      |
+---------+------------------------------+---------------+
|       1 | admin@w3cyberlearning        | d4Z6Jm1L5KLY6 | 
|       2 | user@w3cyberlearning         | d4yOXqnMb09iw | 
|       3 | sophal@w3cyberlearning       | d4elKQV.j6LcQ | 
|       4 | yiling@w3cyberlearning       | d4TBKG4h5SUkY | 
|       5 | yiling1@w3cyberlearning      | d4J/AM81hPu5. | 
|       6 | kwan@w3cyberlearning         | d4Z.cnO8IUAoA | 
|       7 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       8 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|       9 | kwan1@w3cyberlearning        | d4NthJ11wK0KQ | 
|      10 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      11 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      13 | wu@w3cyberlearning           | d4yk8ErVmdF1w | 
|      14 | [email protected]     | d4D9O7zfTlRZE | 
|      16 | [email protected] | 82hngl1f0jsxA | 
|      17 | [email protected]                  | 82VgBddnErejI | 
+---------+------------------------------+---------------+
15 rows in set (0.00 sec)

using LIMIT to return only one record or the first record

mysql> SELECT * FROM user2 LIMIT 1;
+---------+-----------------------+---------------+
| user_id | loginEmail            | password      |
+---------+-----------------------+---------------+
|       1 | admin@w3cyberlearning | d4Z6Jm1L5KLY6 | 
+---------+-----------------------+---------------+
1 row in set (0.00 sec)

the same as above

• Use order by ASC

mysql> SELECT * FROM user2 ORDER BY user_id ASC LIMIT 1;
+---------+-----------------------+---------------+
| user_id | loginEmail            | password      |
+---------+-----------------------+---------------+
|       1 | admin@w3cyberlearning | d4Z6Jm1L5KLY6 | 
+---------+-----------------------+---------------+
1 row in set (0.00 sec)

using LIMIT to return only one record and it is the last record

• ORDER BY user_id DESC is the order by using descending order.

mysql> SELECT * FROM user2 ORDER BY user_id DESC LIMIT 1;
+---------+-------------+---------------+
| user_id | loginEmail  | password      |
+---------+-------------+---------------+
|      17 | [email protected] | 82VgBddnErejI | 
+---------+-------------+---------------+
1 row in set (0.00 sec)

using LIMIT to display only 3 records

• LIMIT 2 (for two records)
• LIMIT 3 (for three records)
• LIMIT 40 (for 40 records)
• LIMIT 100 (for 100 records)

mysql> SELECT * FROM user2 LIMIT 3;
+---------+------------------------+---------------+
| user_id | loginEmail             | password      |
+---------+------------------------+---------------+
|       1 | admin@w3cyberlearning  | d4Z6Jm1L5KLY6 | 
|       2 | user@w3cyberlearning   | d4yOXqnMb09iw | 
|       3 | sophal@w3cyberlearning | d4elKQV.j6LcQ | 
+---------+------------------------+---------------+
3 rows in set (0.01 sec)

using LIMIT to display a range of record (start from row 4 and get 6 records)

• Retrieve start after row 3, and get 6 records

mysql> SELECT * FROM user2 LIMIT 3,6;
+---------+-------------------------+---------------+
| user_id | loginEmail              | password      |
+---------+-------------------------+---------------+
|       4 | yiling@w3cyberlearning  | d4TBKG4h5SUkY | 
|       5 | yiling1@w3cyberlearning | d4J/AM81hPu5. | 
|       6 | kwan@w3cyberlearning    | d4Z.cnO8IUAoA | 
|       7 | kwan1@w3cyberlearning   | d4NthJ11wK0KQ | 
|       8 | kwan1@w3cyberlearning   | d4NthJ11wK0KQ | 
|       9 | kwan1@w3cyberlearning   | d4NthJ11wK0KQ | 
+---------+-------------------------+---------------+
6 rows in set (0.00 sec)

Example for sample pagination

• Display 3 pages for each page
• LIMIT 0,3 (start first page and three records)
• LIMIT 3,3 (start third page and three records)
• LIMIT 6,3
• LIMIT 9,3
• LIMIT 12,3
• LIMIT 15,3

...

mysql> SELECT * FROM user2 LIMIT 0,3;
+---------+------------------------+---------------+
| user_id | loginEmail             | password      |
+---------+------------------------+---------------+
|       1 | admin@w3cyberlearning  | d4Z6Jm1L5KLY6 | 
|       2 | user@w3cyberlearning   | d4yOXqnMb09iw | 
|       3 | sophal@w3cyberlearning | d4elKQV.j6LcQ | 
+---------+------------------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM user2 LIMIT 3,3;
+---------+-------------------------+---------------+
| user_id | loginEmail              | password      |
+---------+-------------------------+---------------+
|       4 | yiling@w3cyberlearning  | d4TBKG4h5SUkY | 
|       5 | yiling1@w3cyberlearning | d4J/AM81hPu5. | 
|       6 | kwan@w3cyberlearning    | d4Z.cnO8IUAoA | 
+---------+-------------------------+---------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM user2 LIMIT 6,3;
+---------+-----------------------+---------------+
| user_id | loginEmail            | password      |
+---------+-----------------------+---------------+
|       7 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | 
|       8 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | 
|       9 | kwan1@w3cyberlearning | d4NthJ11wK0KQ | 
+---------+-----------------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM user2 LIMIT 9,3;
+---------+--------------------+---------------+
| user_id | loginEmail         | password      |
+---------+--------------------+---------------+
|      10 | wu@w3cyberlearning | d4yk8ErVmdF1w | 
|      11 | wu@w3cyberlearning | d4yk8ErVmdF1w | 
|      13 | wu@w3cyberlearning | d4yk8ErVmdF1w | 
+---------+--------------------+---------------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM user2 LIMIT 12,3;
+---------+------------------------------+---------------+
| user_id | loginEmail                   | password      |
+---------+------------------------------+---------------+
|      14 | [email protected]     | d4D9O7zfTlRZE | 
|      16 | [email protected] | 82hngl1f0jsxA | 
|      17 | [email protected]                  | 82VgBddnErejI | 
+---------+------------------------------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM user2 LIMIT 15,3;
Empty set (0.00 sec)

mysql> 

How to calculate pagination

• Display 6 records for each page

LIMIT 0,6
LIMIT 6,6
LIMIT 12,6
LIMIT 18,6
LIMIT 24,6
LIMIT 30,6
LIMIT 36,6
...

calculate example 1

 0x6,6 ==>0,6  
 1x6,6 ==>6,6
 2x6,6 ==>12,6
 3x6,6 ==>18,6
 4x6,6 ==>24,6
 5x6,6 ==>30,6
 6x6,6 ==>36,6

calculate example 2

• Display 7 records for each page

 0x7,7 ==>0,7  
 1x7,7 ==>7,7
 2x7,7 ==>14,7
 3x7,7 ==>21,7
 4x7,7 ==>28,7
 5x7,7 ==>35,7
 6x7,7 ==>42,7