MySQL: id BETWEEN start AND end Instead of LIMIT start, step For Better Database Performance

• inShare

Suppose you have a ultra large table of 1 million records and you need to page all those records across 10,000 web pages with 100 records on each page. For page 2, the most straightforward approach is undoubtedly:

SELECT * FROM table LIMIT 100, 100

However, this query would introduce performance issues when the starting limit is getting larger or the starting row is in the latter half of all the records because of the simple fact that MySQL will have to count its way down all the records to the exact number to return you the results. For instance, the query below will be a major liability to the database server:

SELECT * FROM table LIMIT 800000, 100

With a staggering 800,000 records to count before determining which the 800,000th record is, blame the lagging performance on the LIMIT xx, xx approach.

SOLUTION

Instead, you should rely on existing index to carry out the task – in fact, you should always favor the way a table is indexed and design queries based on them. For example, the table in question may very probably have a primary key which is usually named ‘id’.

If all records are continuous in ‘id’ and the table isn’t manipulated in a way that deletes rows often, you can easily switch to the following approach for much better mysql performance:

SELECT * FROM table WHERE id BETWEEN 101 AND 200

which returns the exact same results as does:

SELECT * FROM table LIMIT 100, 100

but much faster because it has utilized the id primary key index.