I've had phpBB integrated into my site for a while now. We just recently switch from a managed hosting solution to a VPS, giving us root access. MySQL is running painfully slow and consuming far more than its fair share of CPU, and one of the problems we've identified is with the phpBB integration, as it's at the top of every page.
The following query is routinely executed:
SELECT COUNT(session_id) AS sessions
FROM [sessions table]
WHERE session_user_id = 1
AND session_time >= [timestamp];
The problem is that because this is run at the top of every page, there are more than 13000 rows in the sessions table, and even with the WHERE clause it's still doing a full-table scan, since MySQL doesn't use indexes on range queries.
Running an EXPLAIN on the query shows it scanning 10000 of the 13000 rows, and adding a FORCE INDEX() on either session_time or session_user_id bumps it up to a true full-table scan of all the rows.
For reference, the data in the table looks like:
24a9f2ec915078b9c9caaf06612c7a7f, 1, 0, 1299977332, 1299977332, 1299977603, 220.127.116.11, Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US) Ap..., ../index.php, 1, 0, 0
There is no need to be storing this data; it serves me no purpose at all. But adding these records to the sessions table, and then having to scan all those extra records is proving to be incredibly expensive.
What is the suggested method for disabling the session behavior for anonymous users?