Re: Configuration Advice

On 18-1-2007 0:37 Adam Rich wrote:
> 4) Complex queries that might take advantage of the MySQL "Query Cache"
> since the base data never changes

Have you ever compared MySQL's performance with complex queries to
PostgreSQL's? I once had a query which would operate on a recordlist and
see whether there were any gaps larger than 1 between consecutive
primary keys.

Normally that information isn't very usefull, but this time it was.
Since the data was in MySQL I tried several variations of queries in
MySQL... After ten minutes or so I gave up waiting, but left my last
version running. In the mean time I dumped the data, reloaded the data
in PostgreSQL and ran some testqueries there. I came up with a query
that took only 0.5 second on Postgres pretty soon. The query on MySQL
still wasn't finished...
In my experience it is (even with the 5.0 release) easier to get good
performance from complex queries in postgresql. And postgresql gives you
more usefull information on why a query takes a long time when using
explain (analyze). There are some draw backs too of course, but while we
in our company use mysql I switched to postgresql for some readonly
complex query stuff just for its performance...

Besides that, mysql rewrites the entire table for most table-altering
statements you do (including indexes). For small tables that's no issue,
but if you somehow can't add all your indexes in a single statement to a
table you'll be waiting a long time more for new indexes than with
postgresql. And that situation isn't so unusual if you think of a query
which needs an index that isn't there yet. Apart from the fact that it
doesn't have functional indexes and such.

Long story short: MySQL still isn't the best performer when looking at
the more complex queries. I've seen performance which made me assume it
can't optimise sequential scans (when it is forced to loop using a seq
scan it appears to do a new seq scan for each round in the loop...) and
various other cases PostgreSQL can execute much more efficiently.

So unless you run the same queries a lot of times and know of a way to
get it fast enough the initial time, the query cache is not much of a help.

Best regards,

Arjen