[thelist] a question on MYSQL LIMIT clause - large tables.

[VOLKAN ÖZÇELİK]

>
> I just did a very quick test with not that big of a database, and my query
> seemed to perform equally to your second method (using OFFSET). As
>you thought, your first query (WHERE id>9000) was actually the fastest,
>

Thanks Suni,

I conducted some further research on the issue as well.

Here is what I found in conclusion.

1. If you are sorting indexed fields and then applying LIMIT with
offset or using (WHERE id>9000) act similar in terms of performance.

2.If you are not using an index on your sort criteria, then using
(WHERE id>9000) is several hundred times faster (<1 second versus >1
minute) than using LIMIT with offset.

3. Even if you use indexes, in some cases the latter (WHERE > 9000)
may be considerably faster.

In that case:
   3.1. Make sure the query utilizes indexes. EXPLAIN your query and
USE INDEX of FORCE INDEX if necessary.
   3.2. It may be helpful to use a "covering index" that covers the
fields you sort. (this may consume some disk space but result in a
performance increase)

Though I've not benchmarked this findings with real data. I may do
some kind of sandbox/testcase after I catch up my deadline(s).

After having read dozens of related stuff, forums etc; I think I will
use (WHERE > 9000) to be on the safe side instead of offsetting.

Cuz, I don't have much time (who does have, anyway :) )
And
i.  I don't want to wake up in the midlle of the night with complaints
from my client when my data grows to several hundred megabytes,
gigabytes or whatever.

ii. The code may perform as expected now. But if the client asks "I
want my data to be sorted some other way around..." (clients always do
ask, and clients are always right ;) ) I do not want to think how to
optimize my truck-load of data using the new conditions.

Thanks again for your reply.

Cheers,
--
Volkan Ozcelik
+>Yep! I'm blogging! : http://www.volkanozcelik.com/volkanozcelik/blog/
+> My projects/studies/trials/errors : http://www.sarmal.com/