[Wolves] mysql select help

[Chris Ellis]

Hi Mark

On 13 March 2012 11:35, Mark Rogers <mark at quarella.co.uk> wrote:
> On 13/03/12 01:52, Chris Ellis wrote:
>>
>> You can also use an aggregate function in your subquery rather than a
>> order and limit, this should be more efficient: SELECT * FROM phonelist
>> WHERE trydate = ( SELECT MIN( trydate ) FROM phonelist WHERE trydate >
>> DATE(NOW()) )
>
>
> Good point; it may well be that MySQL will optimise the query to achieve the
> same results but using MIN makes more sense than order and limit. Also,
> making sure that trydate is indexed will make a huge difference.
>
>

I would be sceptical of the planners ability to make that kind of
optimisation.  Generally
sorts are post processing on the query, along with limits.  This will
require more work than
an aggregate and may need to spill to disk, certainly for large result sets.

Given the number of rows we are talking, an Index would make little
difference, possibly reduce
the performance.  The table will be small, it will fit in RAM, a
sequential read of the table will be far
quicker than the random IO of an index scan.

This is certainly true for PostgreSQL, the PostgreSQL planner will
rarely do an index scan on a
small table, for those reasons.

Chris