LIMIT clause

Sometimes tables contain thousands of rows and if you run a SELECT statement to display all rows, you will impact performance and possibly even crash the system. However, you can use the LIMIT clause to choose how many rows will be returned in a query. Here is an example:

mysql> SELECT * FROM employees LIMIT 5;
 +--------+------------+------------+-----------+--------+------------+
 | emp_no | birth_date | first_name | last_name | gender | hire_date |
 +--------+------------+------------+-----------+--------+------------+
 | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
 | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
 | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
 | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
 | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
 +--------+------------+------------+-----------+--------+------------+
 5 rows in set (0.01 sec)

The employees table mentioned above contains thousands of rows. Using the LIMIT clause, we were able to display only the first 5 rows.

You can also specify the starting position. For example, to start at the position 2 and return 7 rows, we can use the following command:

mysql> SELECT * FROM employees LIMIT 2,7;
 +--------+------------+------------+-----------+--------+------------+
 | emp_no | birth_date | first_name | last_name | gender | hire_date |
 +--------+------------+------------+-----------+--------+------------+
 | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
 | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
 | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
 | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
 | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
 | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
 | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
 +--------+------------+------------+-----------+--------+------------+
 7 rows in set (0.00 sec)