0

I've run this two queries using mysql work bench:

  1. SELECT bussId FROM tempBusiness order by bussId ASC limit 250000 ,1

  2. SELECT bussId FROM tempBusiness order by bussId ASC limit 1 ,1

The first one doesn't finish running, the second one returns the bussId very fast (0.096 sec / 0.000016 sec).

Here is the explain for the above two queries:

1.

enter image description here

2.

enter image description here

What may be the problem ?

jarlh
  • 42,561
  • 8
  • 45
  • 63
david
  • 3,310
  • 7
  • 36
  • 59
  • @popovitsj no ! it shouldn't return 250000 row, it should return one row but the offset is 250000 ! – david Apr 14 '15 at 12:50
  • Thats usual since when you limit data from a big number the optimizer probably will do a full table scan, however this could be achieved in another way using range filter on an indexed column something as `SELECT bussId FROM tempBusiness where bussId >250000 order by bussId limit 1 ` – Abhik Chakraborty Apr 14 '15 at 12:50
  • @AbhikChakraborty . . . The optimizer should really be doing a full *index* scan, not a full *table* scan. That appears to be what it is doing. – Gordon Linoff Apr 14 '15 at 12:51
  • @david that's what I said. But I guess there shouldn't be a difference in that respect between the two queries, so I don't think that it has to do anything with memory on second thought. – wvdz Apr 14 '15 at 12:52
  • @popovitsj for smaller number it's working , but why when the number big , it take a long of time to return the result , I try with other table and it's return the one result fast . – david Apr 14 '15 at 12:55
  • possible duplicate of [Why does MYSQL higher LIMIT offset slow the query down?](http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down) – wvdz Apr 14 '15 at 13:00

1 Answers1

0

In order to get to an offset of N in an indexed column, it has to scan through the index sequentially. If the offset is small, this finishes quickly. But to get to a very large index like 250000, it has to load many entries from the index into memory.

I don't think there's any good way to optimize this. If you have sequential ID fields, you could use WHERE id = 250000 instead of using LIMIT.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • thank you for your answer, but why when i try the same query but with different table name in another table , mysql return one result very fast . – david Apr 14 '15 at 12:57
  • which index, are you referring to the primary key ? – david Apr 14 '15 at 13:00
  • Yes, the primary key is an index. `EXPLAIN` says it's using the index. – Barmar Apr 14 '15 at 13:00
  • so maybe at tempBusiness table , the index didn't finish loading yet ? – david Apr 14 '15 at 13:02
  • Right. What happens if you try two queries in a row with large offsets? – Barmar Apr 14 '15 at 13:04
  • if the query at tempBusiness table it will not finish running, but if it at another table it return the result very fast ! – david Apr 14 '15 at 13:06
  • but How to check if my index doesn't finish loading ? – david Apr 14 '15 at 13:07
  • There's no way to check. It loads indexes as it needs them. – Barmar Apr 14 '15 at 13:10
  • so if I create an index, may it be created after one month , when mysql want to use a query that will use that index ? – david Apr 14 '15 at 13:11
  • The index is created immediately. It's just not loaded into RAM until it's needed. – Barmar Apr 14 '15 at 13:14
  • aaa so, if I insert row into tempBusiness, I will enforce mysql to use the primary key index, so if I run again query number one, mysql will use the index (because it already at the memory) then it will return the result fast ! right ? – david Apr 14 '15 at 13:18
  • I don't think so. Adding to the index doesn't require loading the whole thing into memory. – Barmar Apr 14 '15 at 13:19
  • thank you but my final question : should my sql load the index into the ram to use it, I mean it can't use the index if it not loaded into the ram ? – david Apr 14 '15 at 13:22
  • The only way that computers can use data is to load it into memory. That's how computers work. – Barmar Apr 14 '15 at 13:25
  • but where it's stored , it's not stored into the memory ? – david Apr 14 '15 at 13:27
  • No, it's stored on disk. – Barmar Apr 14 '15 at 13:29
  • aaa ok thank you very much for you help, so now i will search the difference between disk and memory (ram) ! – david Apr 14 '15 at 13:30
  • How can a computer programmer not know the difference between disk and RAM? Disk is where you have named files. RAM is where the variables in your programs are. – Barmar Apr 14 '15 at 13:37