Topic: More index issues
I haven't had time to really investigate any of these issues, but we've been having some speed issues, and there are just some of the culprits I've found. I mainly wanted to note what they were and what I did before I forgot. So ye be warned, this might just be me being index happy, but I seem to be in a never ending battle with MySQL...
The first issue I encountered isn't a huge deal since it's only from the mod/admin side, but still takes a noticeable hit. If you do a look up on a certain IP address, it can take some time, since the poster_ip column in the posts table isn't indexed. Indexing that seems to have fixed that up nicely.
Second issue occurs at least on the show new posts since last visit page, and maybe elsewhere. The problem arises in that it's using the forum_id index, which seems to make snese, but then when it has to go sorting it by the last_post column, that kills it. It seems as though adding a index to the last_post column will fix things nicely. It almost seems like a multiple index between the forum_id and last_post would be better, but things seem nice and peachy with just the last_post index (10-30 seconds down to a fraction of a second). Hm, and is the forums table even need to be joined on? Couldn't you just join the forum_perms directly to the topics table? Wait, this is all giving me a sense of deja vu.. Did I already go through all of this the last time I brought up indexes? Hm..
The last problem, which I haven't been able to tackle yet is the viewtopic.php. The query executes fine for relatively forums without a lot of posts, but in forums with a large number of posts, it can take a few seconds to execute. It's first limiting things by the forum_id, which is good, but then when it comes to sorting 8,000 records based on the sticky and last_post column, things turn not so nice. I tried a multi-column index, but MySQL didn't seem to like that.
In any case, these all might actually be due to my screwing around with the server and other funkiness. But, hey, maybe 1 out of 3 might help. I'm just doing some midnight rambling, but I'll try to confirm and further debug these over this weekend.