QUERY speed with limit and millionth records

Question

Hi, I have a dm 7milion entry table for checking query speed.

I tested my 2 queries, which are the same query with different restriction parameters:

request 1 -

SELECT * FROM table LIMIT 20, 50; 

request 2 -

 SELECT * FROM table LIMIT 6000000, 6000030; 

Request lead time:

• query 1 - 0.006 s
• request 2 - 5,500 s

In both of these queries, I get the same number of records, but in the second case it takes longer. Can someone explain the reasons for this?

Answer 1

Despite being too close, my assumption is that this is because the first query only needs to read the 50th record to return the results, while the second query needs to read six million before returning the results. Basically, the first request just closes faster.

I would suggest that this has an incredible amount associated with the composition of the field types and table keys, etc.

If a record consists of fields of a fixed length (for example, CHAR versus VARCHAR), then the DBMS can simply calculate where the nth record begins and goes there. If its length is variable, then you will need to read the entries to determine where the nth entry begins. Similarly, I would suggest that tables that have matching primary keys will query faster than those that don't have such keys.

Answer 2

I think the slowdown is due to the fact that you use constraints with offsets and query the table without additional context for indexing. His possible first is simply faster, because he can move to offset faster.

Answer 3

This is the difference between returning 50 rows and 60,000,000 rows (or ~ 1 million rows, since you said there were only 7 million rows).

With two arguments, the first argument determines the offset of the first line to return , and the second indicates the maximum number of lines to return . The start line offset is 0 (not 1):

SELECT * FROM tbl LIMIT 5.10; # Extract lines 6-15

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

In addition, I think you are looking for 30 lines of lines, so your queries should use 30 as the second parameter in the limit clause.

 SELECT * FROM table LIMIT 20, 30; SELECT * FROM table LIMIT 6000000, 30;