savetheinternet/Tinyboard — pull request #94 by Macil (2013-01-19 07:08:29+00:00)
Title: Add multi-column key to optimize rebuilding index pages

This adds a key on (`thread`, `id`) to posts_%s to optimize this query, which is called a lot of times on every post to recreate the index pages:

$posts = prepare(sprintf("SELECT * FROM `posts_%s` WHERE `thread` = :id ORDER BY `id` DESC LIMIT :limit", $board['uri']));

This gave me a noticeable speed-up. (Using the cache also helps this performance point a lot, though I haven't gotten around to setting that up, and it seems to be broken/disabled in the current git commit.)

This SQL query will need to be run on each of the posts_* tables, so it probably should go somewhere into install.php:

create index `thread_id` on posts_%s (`thread`, `id`);