Hi Micha,

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);

mlf2_entries.last_reply is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`last_reply`);

The computer that I use for these tests is busy with another task at the moment but I will test it tomorrow.

includes/main.inc.php, line 339: about 600 milliseconds
includes/main.inc.php, line 336: about 120 milliseconds

Are you sure? mlf2_entries.pid, mlf2_entries.category as well as mlf2_entries.spam are indexed.

Yes. Both the queries at line 336 and 339 do a COUNT(*).

This page says that COUNT(*) is slow on InnoDB tables:
http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql

Two quotes from that page:
InnoDB can be very slow to perform count(*) type queries, cause it is designed to allow for multiple concurrent views of the same data. So at any point in time, its not enough to go to the index to get the count.

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool.

----

The difference between the query at row 336 and the query at row 339 is that the query at row 336 returns COUNT = 28 000 and the query at line 339 returns COUNT = 192 000. This explains why the query at line 336 is five times faster.

Best regards,
Daniel