ORDER or NOT

[jaymc]

I have a query that is going to look for 900 rows in a table that has 150,000 rows

 

Now, if i query the table with a LIMIT 0,900 it will go through every row until it finds 900 that match its criteria

 

So in theory it may check 100,000 rows to find its 900

 

However, I know that if I ORDER the table by `lastaction` those 900 rows will all be at the bottom

 

Will it slow the query down more to use ORDER or to have it check all those rows until it finds its 900 matches

[btherl]

Have you timed the queries?

 

If you query without ordering, then mysql will simply return the first 900 rows (as stored on disk), which will be quite fast.  That's fine as long as the first 900 rows are what you want.

 

If you use order by, mysql can use an index on the order condition to find the first 900 rows in that ordering.  I would expect it to be slower though, as each row may be in different pages on disk, and the index must also be read in.

 

Benchmark it and see

 

An order by without an index will be much much slower, as it must sort the entire table before taking the first 900.

[jaymc]

So in theory ORDER would slow it down?

[artacus]

Yes, it probably will.

[fenway]

But I'm unclear as to why you have the "choice" to use the order by.

[artacus]

Because the way records are entered into the db creates a pseudo chronological order by.

[fenway]

Ah yes, we've encountered this before.