wlcna wrote:
> The timings had an equal playing field, which in all three cases was
> virtually no indexing, but you think this explains innodb taking 36
> seconds versus postgres 5-6, and do you think it explains myisam going
> slower than postgres? These are the points I'm raising.
So did you try adding indexes to see if that helps? I've seen on various
I just tried adding a few indexes where I thought they should have been
needed on the innodb, and the time did improve vastly I must say.
It's now .04 seconds!
I guess I feel a little more confident about using it now...**I*think*I
figured it would probably only drop to 10 seconds.**:)
I knew indexes would help, but I still wonder why it's so much slowerthan
the others without any such extra help.
The slowness is often because you're compounding large non-indexed tables
with other large non-indexed tables. Say for example you have two tables of
20,000 records and you join them on a non-indexed column. It effectively
has to do 20,000 x 20,000 comparisons (ie 400 million) to try to find what
you're wanting. (OK so it's probably not quite as inefficient as that, but
I'm trying to prove a point here). If you have those columns indexed then
it should be able to seek to the appropriate records with only a few looks
at the indexes of each table.
Here's a real world example (well the tables and number of records are,but
I'd never run this query normally). The DBR_Plate table has 286,633 records
in it. The Plate_Properties table has 94,019
EXPLAIN SELECT *
FROM DBR_Plate p
INNER JOIN Plate_Properties pp ON p.Plate_ID = pp.Plate_ID
table type possible_keys key key_len ref rows Extra
pp ALL PRIMARY NULL NULL NULL 94019
p eq_ref PRIMARY PRIMARY 6 pp.Plate_ID 1
Running this query (ie removing the explain part) took 0.0201 seconds
I removed the indexes and this happens:
table type possible_keys key key_len ref rows Extra
p ALL NULL NULL NULL NULL 286633
pp ALL NULL NULL NULL NULL 94019 Using where
Running this query takes... well I gave up after waiting about 5 minutes and
changed the query to select a single record based on the Plate_ID value..
This query took 0.7223 seconds. Running it again after recreating the
indexes took just 0.0006 seconds.
You can see the massive improvement, especially given I could select all
records with indexes 36 times faster than selecting a single record when
there were no indexes, and selecting a single record was 1200 times faster
with indexes than without. The value of indexes... :)
--
Chris Hope - The Electric Toolbox -
http://www.electrictoolbox.com/