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.