mysql v4.0.16:
I had been using mysql with innodb and thought that was fine, until i used
it for something requiring a few - perhaps slightly involved - joins, and
have now seen the performance become totally unacceptable.
I have a query that takes over 35 seconds using mysql and innodb, for
reasons that are completely a mystery to me, in a result set consisting of
only a handful of items.
I can't go into exact specifics, but I have 2 tables being joined in a
"typical" and very simple manner (e.g. customer to order) and then I have to
do two further joins from that onto a single pretty large table (over 25,000
rows). This large table is being joined onto two times using a standard
inner-type join, i.e. all the 25,000 rows are not coming back in the query,
in fact the number of records coming back is quite small.
microsoft access timings
- 1-2 second perhaps, because 2-4 seconds is the result on a MUCH SLOWER
machine than the Linux machine in question. I'm being conservative because
the Linux machine's processor is 4x faster than the 2-4 second machine. On
another machine that has a 60% faster processor than the Linux one, the
query is essentially instantaneous, definitely less than a second.
mysql timings (using mysql interpreter and timings therefrom directly
(<mysql -p> from command line):
- myisam
- 6.61 seconds
- 6.81 seconds
- innodb
- 35.79 seconds (first time after create table/inserts/etc)
- 35.70 seconds
postgresql timings (using postgresql interpreter directly (<psql> from
command line):
- 6 seconds (first time after create table/inserts/etc)
- 5 seconds
- 5 seconds
MySql is setup on a Linux machine to take over like half of the resources as
well, so I don't think it's a problem of resource starvation.
I thought myisam was supposed to be fast. I would have thought it could go
at least as fast as access. Even postgresql is quicker.
Any comments? I'm now wondering if mysql is total crap and even if postgres
is pretty bad, since it too looks quite poky, though I'm thinking now I
should probably have been using postgres all the time. The postgres setup
is just straight out of the box, not "tuned" at all for performance, whereas
the mysql one is supposed to be somewhat tuned that way, configuration file
is in the "medium" mode as they describe it in the default config files,
wherein it hogs a chunk of the machine's RAM.
