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
