Can anyone help with my understanding of the processing score reported
by EXPLAIN and the way it relates (or doesn't) to processing time?
I test-run queries against an old, slow linux box in order to help
spot time or processor consuming queries, because I don't understand
the figures that EXPLAIN returns :(
example: explain select a.field1, b.field1 from table1 a join table2 b
on field a.field2=b.field2 limit 100;
sequential scan on table2 rows 12900 width 30
cost 0.00..249
(actual elapsed time 3.4 secs)
example: explain select a.field1, b.field1 from table1 a join table2 b
on field a.field2=b.field2 where b.field1='xxx' limit 100;
index scan on table2 using index_xxx rows 26 width 30
cost 0.00..23
(actual elapsed time 6.4 secs)
Why is the actual elapsed time higher for the second example?
Is the "figure of merit" given by explain attempting to describe the
relative processing requirement to run the query, or is it (as I
suspect) the requirement to setup the structure of the query, ready to
actually do the work - and the work processing requirement will vary
dependant upon the data, memory available to perform match filtering
etc?
I would like to understand this better, as otherwise the only real
means I can think of for tuning queries in order to minimise
processing requirements is the long winded reported query time tests
based on example queries.
--
Best regards,
Andy mailto:lbc@telecam.demon.co.uk