Suppose there are 1,000,000 entries in a table. I need to find the record 999,999.
Will ORDER BY affect the sampling rate?
At work, they tell me that with ORDER BY ASC request will have to process the lines from 0 to 999,999, and if DESC , then only from 1,000,000 to 999,999, that is, one line.

  • one
    So you would take and check / test this statement. But in general, yes, the difference in execution speed is really big. - Regent
  • find record how? leave it only with the help of limit - then yes, the records must first be sorted and then skipped as much as specified. And another question is how the sorting will be performed, maybe there is an index and you can quickly walk through it without lifting the basic data. And the sorting itself will eat the lion's share of time, since if there is no index to understand that this record is the first one, you still need to review all the others in order to understand that they all should be behind it - Mike
  • @Mike but still the SELECT * FROM ChatMessages ORDER BY text ASC LIMIT 125551, 10 runs for 650-800ms, and the SELECT * FROM ChatMessages ORDER BY text DESC LIMIT 0, 10 for 150-190ms. Indices in both queries are not used. - Regent
  • @Regent And I did not say that they will be the same in time. Actually, I started the phrase by saying that I need to skip something. And it's clear that skipping takes time - Mike
  • @Mike, it turns out that sorting all records in a table by a field that is not in the index takes about 150 ms, and to skip 125551 records in already sorted data — from a good 500 ms. Somehow it is strange, in my opinion, it turns out. It will be necessary to read something about this: when I started using it in the code, I didn’t really think about why it works many times faster. - Regent

1 answer 1

The sorting direction does not affect anything. There is no difference: take numbers from the end or from the beginning. But if you do a LIMIT 125551, 10 selection, and if there are additional conditions (or as you do not have indices), mysql will select and sort 125551 rows and select the latter from them.

With regards to the lack of indexes, it’s not a fact that they were not cached, even if they are not there.