mysql vs postgres vs microsoft access

[wlcna]

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.

[Bill Karwin]

I wouldn't consider comparison between MS Acess and any true RDBMS
product to be a valid comparison. They are intended to solve different
problems, so they have different constraints in their implementation.

That said, if MS Access solves your application needs and it is faster,
then use MS Acess. But be sure to adopt a rigorous data backup policy! ;-)

But as for the efficiency of MySQL, have you tried using the EXPLAIN
command to see how the RDBMS is optimizing your query? Do you have
appropriate indexes established to help the query in your test case?

Have you read through the optimization tips on Mysql.com and tried some
of the suggestions?
http://dev.mysql.com/doc/mysql/en/My...imization.html
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

I don't recommend measuring performance of queries against an InnoDB
table immediately after a data load. There is likely to be some
performance hit as the various caches are primed. Subsequent queries
might become faster when they are able to take advantage of cached
indexes and data.

Regards,
Bill K.

[Chris Hope]

wlcna wrote:

I had been using mysql with innodb and thought that was fine, until iused
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

[snip]

You do have indexes on the columns you are joining, don't you?

You can also use "explain query" where "query" is the query you are running.
This gets MySQL to tell you exactly how it's going to run the query andyou
can work out from the results how much work is actually being done, and
where you need to optimise it.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[wlcna]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cj*********@enews4.newsguy.com...

I wouldn't consider comparison between MS Acess and any true RDBMS product
to be a valid comparison. They are intended to solve different problems,
so they have different constraints in their implementation.

Well, since as far as I understand, myisam does table-level locking and
doesn't support lots of common features of RDBMS systems, like stored
procedures and many other things, many would say it is not a "real" RDBMS
either, so I think comparison to access is fairly valid.
That said, if MS Access solves your application needs and it is faster,
then use MS Acess. But be sure to adopt a rigorous data backup policy!
;-)

MS Access doesn't solve the application needs, but it's a performance
baseline.
But as for the efficiency of MySQL, have you tried using the EXPLAIN
command to see how the RDBMS is optimizing your query? Do you have
appropriate indexes established to help the query in your test case?

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.
Have you read through the optimization tips on Mysql.com and tried some of
the suggestions?
http://dev.mysql.com/doc/mysql/en/My...imization.html
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
http://dev.mysql.com/doc/mysql/en/InnoDB_tuning.html

Optimization is something I would do when I find a performance problem.
This is a definite problem but it is so large with innodb that it is causing
me to dump innodb altogether rather than looking for optimization solutions.
I'm currently switching to postgres, which while still poky, presumably once
optimized will be hopefully tolerable.
I don't recommend measuring performance of queries against an InnoDB table
immediately after a data load. There is likely to be some performance hit
as the various caches are primed. Subsequent queries might become faster
when they are able to take advantage of cached indexes and data.

I've had the data that caused the original problem for days now and the
innodb still takes the same length of time. I just timed it again and it's
36.03 seconds.

[Chris Hope]

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
different database systems massive differences in adding indexes. Therewas
one SQL Server database table which had several million records and would
grind through the queries taking up to 15 minutes to run some of our more
complex queries. We took a look at the way some of the tables and queries
were structured and modifed the indexes and the time for these queries
dropped to seconds.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[wlcna]

"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:Y2*******************@news.xtra.co.nz...

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 slower than
the others without any such extra help.
different database systems massive differences in adding indexes. There
was
one SQL Server database table which had several million records and would
grind through the queries taking up to 15 minutes to run some of our more
complex queries. We took a look at the way some of the tables and queries
were structured and modifed the indexes and the time for these queries
dropped to seconds.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Chris Hope]

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/

[wlcna]

"wlcna" <wl***@nospam.com> wrote in message
news:0r*****************@newssvr19.news.prodigy.co m...

"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:Y2*******************@news.xtra.co.nz...

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!

Further update on the other alternatives, fwiw:

mysql w/myisam - 0.00 seconds every time!
postgresql - 11.105, then 0.820, then 0.818 seconds. (these timings have
been with latest 8.0 beta, beta 3, perhaps non-beta older version might be
faster?)

[...]

[michael newport]

if you want a mature open source database then check out ingres at ca.com