Phil,
Just about any query will usually take a few milliseconds (try SELECT 1;
to see the absolute lowest), and so 12 ms is probably about as good as
you can get. For my own part, I consider 50 ms good enough for any
query that is not run inside of a loop. If you want to write suitably
efficient code/SQL for this, I suggest filling your tables with more
data (say, 10 times as much as you have now) and then see how the
timings work.
Are you already working with what you would consider a "typical" data
size? Or is it smaller than what someone would typically have?
If you post any more timings on this list, please post the EXPLAIN
ANALYZE as well. This allows us to see what plan the planner picked,
how much time each step took, and how many rows were actually affected.
To get the EXPLAIN ANALYZE, just type EXPLAIN ANALYZE <your query goes
here> and copy the output.
Regards,
Paul Tillotson
Phil Daintree wrote:
>I can also do the same thing without sub-queries - I messed about some more
>since I was keen to ensure backward compatibility with prior versions of
>mysql that have left/right joins but no subqueries ... quite a bit quicker
>still!
>
>Query took 0.0037 sec - 1/10th of the sub-query time.
>
>SELECT chartmaster.accountcode, periods.periodno
>FROM chartmaster INNER JOIN periods ON True
>LEFT JOIN chartdetails ON chartmaster.accountcode = chartdetails.accountcode
>AND periods.periodno = chartdetails.period
>WHERE periods.periodno >=1 AND periods.periodno <=63 AND
>chartdetails.accountcode IS NULL LIMIT 0 , 30
>
>
>In postgres:
>
>SQL executed.
>
>Total runtime: 12.241 ms
>
>Still this is a third of the time of the sub-query route but 4 times longer
>than mysql - this must be an install issue?
>
>
>Thanks again for this idea Paul
>
>phil
>
>
>