Peculiar Query (Page 1 of 2)

maybe since you run a MUCH bigger and more active site it(performance hit due to the "junk") showed up and was more apparent? just a thought.

Well, our forums have been running a day without the DISTINCT and it's been all peachy. No complaints of screwed up topics, and the server is much happier.

But semi-related, the extern.php is full of evil queries as well. A few months ago I tried to use it to show topics on our front page, but it immediately made MySQL go nuts, and slowed the load time for the front page way down. I soon discovered that the main query extern.php was calling was taking around 2 seconds to execute every time. I've finally got around to debugging it, and I think I have a solution. It's rather strange, but it seems to work. The culprit it seems is sorting the results based on the last_post or posted columns. I tried combined indexes, and all that jazz, but those only seemed to marginally increase speed. Strangely enough, it seems like using only a newly created index on the column your sorting by (ie last_post) returns near instant results. The main caveat being that MySQL doesn't want to use that index by default, so you have to force it. All very strange...

SELECT t.id, t.subject FROM topics AS t FORCE INDEX(topics_last_post_idx) INNER JOIN forums AS f ON f.id=t.forum_id LEFT JOIN forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id=3) WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND t.moved_to IS NULL AND f.id IN(4,2,5,29,10,15,16) ORDER BY t.last_post DESC LIMIT 10;

The FORCE INDEX and the associated index being the only part I actually added. That consitently returns results within 0.00-0.01 seconds, compared to the 1-3 seconds previously. It seems like combined indexes would help with at least t.moved_to and t.last_post, but those seemed to have performance problems as well. I think. This all has me slightly confuzzled at this point, as I've been staring at this for too long.

I'm also wondering if you even need a to join the forums table onto this query, since you could easily join the forum_perms table using only t.forum_id. Switching doesn't seem to effect performance at all. Although, actually now that I think about it, since the last_post index is now being used on the topics table, it's probably wiser to join it as a separate table, so you can utilize its own index when restricting forums.

Another oddity was that the INNER JOIN of the forums table seemed to be creating the temporary tables. Switching it to a LEFT JOIN no longer generated temporary tables, but I think it ended up overall slower. Humph. I quit.

Anyway, I'll mess with this some more and clean it up later this week, if you don't beat me to it, and I'll let you know what I find out.

Hm, looks like 4.0.18. Which reminds me that earlier today, I was testing this on my local test server with the latest version of 4.1, and I thought I had discovered a much more elegant solution than all this forcing index crap. Although I think that might have had to do with not restricting which forums the topics belong to. Bah. My brain be fried. I'll tackle this again later this week.

Yeah, it was never as bad for me as everybody else was whining about. But occasionally I would experience some slow downs and MySQL was definitely not completely happy. Since fixing the viewtopic thing, it has been snappier, and MySQL has been acting great.

However, a fair number of temporary tables are still being generated by extern.php, but since we aren't being hammered with those requests, it's certainly manageable. Although, in its current state, extern.php isn't usable on the main website, since with that many hits, it really kills the server.

I was planning on having a closer look at this today. I do remember adding that DISTINCT a long time ago and I doubt I would add it if it didn't do any good. I could be wrong though

Anything to do with sqlite support?

Argh! I can't seem to find it. I've been able to establish that I added it sometime between 2005-11-24 and 2005-11-27, but other than that, I really have no idea. I'm positive it's there for a reason. On the other hand, I haven't been able to reproduce any problems with it taken out.

Me neither

but thats not this query

If it would be easier, I could hook you with an SQL dump of whatever stuff you needed. Unless, you had planned on importing the vBulletin database anyway. Whatever is easiest for you.

Although, I would think extern.php would still generate temporary tables on smaller boards, it's just that the effect wouldn't be as noticeable. But who knows how that crazy MySQL engine works, or if I screwed up indexing somewhere along the way.

I guess it must be, same thing is happening in the test forum here :-/

I knew it! Oh well, it'll have to wait until tonight. I'm off to work now.