hi.

people already familiar with SQL's transaction isolation level and MVCC databases can ignore this post. anyone who doesn't may want to read it.

the problem: a lot of the queries used on zedler are report queries which examine a lot of records (possibly every page, link or revision) to generate the result. not only do these take a long time to run, but they slow down the SQL server for other uses, since InnoDB queries are transactional, and the server must ensure that changes to the database don't affect the query.

in most cases -- like on the production site -- this is what you want, and the performance penalty is acceptable. however, for other uses, you may be more interested in the query completing quickly than having entirely consistent results (especially when your result is going to be several hours out of date anyway).

for this purpose, MySQL provides a command, SET TRANSACTION ISOLATION LEVEL. this allows you to control (for the current connection only) how consistent queries will be. this and other related InnoDB issues are explained in detail at: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html (section 14.2.10.3 in particular) but i'll provide a brief summary here.

the default isolation level is "repeatable read". this means that (mostly) everything you do will be consistent relative to the first query you run in each transaction. if the table changes, your transaction will not see the changes.

the least consistent isolation level is "read uncommitted". this does not perform any locking or multi-versioning for the query. the downside of this is that you may see dirty data (slightly out of date or uncommitted) in the result. however, the advantage is that the query will run much faster.

you can change the current isolation level with the command:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if you're running slow queries on the database, you may like to test this and see how much difference it makes.

you may also want to review the manual sections linked above for more details.

k.