Today SubWolf figured one of the major wedge producers in the php code are a set of SQL queries using some nasty joins. It seems that we need to rewrite all these queries. I think we can get the most efficiency from sub-queries so we avoid the issues of joins entirely. The joins are on some massive tables and always result in writing a tmp table to disk so its very problematic.
So now we are looking at upgrading to MySQL 4.1.1 so we can also take full advantage of table caching. We’re not sure if its going to be an easy deploy because of libraries, we’re using Debian stable which is unpleasant when you’re trying to use modern software.