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
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
