QUERY speed with limit and milion records

Question

Hi i have a 7milion records db table for testing query speed.

I tested up my 2 queries which are the same query with different limit parametres:

query 1 -

SELECT    * 
FROM      table 
LIMIT     20, 50;

query 2 -

SELECT    * 
FROM      table 
LIMIT     6000000, 6000030;

query exec times are:

1. query 1 - 0.006 sec
2. query 2 - 5.500 sec

In both of these queries, I am fetching same number of records, but in the second case it's taking more time. Can someone please explain the reasons behind this?

Answer 1

Without looking into it too closely, my assumption is that this occurs because the first query only has to read to the 50th record to return results, whereas the second query has to read six million before returning results. Basically, the first query just shorts out quicker.

I would assume that this has an incredible amount to do with the makeup of the table - field types and keys, etc.

If a record is made up of fixed-length fields (e.g. CHAR vs. VARCHAR), then the DBMS can just calculate where the nth record starts and jumps there. If its variable length, then you would have to read the records to determine where the nth record starts. Similarly, I'd further assume that tables which have appropriate primary keys would be quicker to query than those without such keys.

Answer 2

I think the slowdown is tied to the fact you are using limits with offsets and are querying the table with no additional context for indexing. Its possible the first is just faster because it can get to the offset quicker.

Answer 3

It's the difference between returning 50 rows and 6000030 rows (or ~1million rows since you said there were only 7million rows).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

http://dev.mysql.com/doc/refman/5.0/en/select.html

Also, I think you're looking for 30 row pages so your queries should be using 30 as the second parameter in the limit clause.

SELECT    * 
FROM      table 
LIMIT     20, 30;

SELECT    * 
FROM      table 
LIMIT     6000000, 30;