Problem with INCREDIBLY slow mysql queries

[bob]

I just updated this forum hxxp:yabb.jriver.com/interact [nonactive] from yabbse 1.5 to SMF 1.0rc2. SE was working well but I was concerned about future security risks. The upgrade went well for the most part but I'm getting some seemingly random LONG pauses, like a minute or so ocassionally. This wasn't a problem with SE. I turned on the slow-queries log in mysql and am seeing stuff like the following in it:

# Query_time: 58  Lock_time: 0  Rows_sent: 0  Rows_examined: 167349

                                        INSERT INTO interact_matches
                                        SELECT DISTINCT hxxp:t.id [nonactive]_TOPIC, hxxp:t.id [nonactive]_FIRST_MSG, t.numReplies, 0 AS is_subject
                                        FROM interact_topics AS t, interact_messages AS m
                                        WHERE hxxp:t.id [nonactive]_TOPIC = hxxp:m.id [nonactive]_TOPIC
                                                AND hxxp:m.id [nonactive]_BOARD IN (2, 3)
                                                AND m.body RLIKE '[[:<:]]install[[:>:]]';

I believe this means 58 SECONDS on the query which would appear to match what I'm seeing.

This is running on an AIX box with MySQL 3.23.58-Max-log, same as what SE was using. 
Forum Stats      
Total Topics: 24604      Total Posts: 170636
DNS resolution is off everywhere.

Any ideas about what could be causing this hesitation??

[Grudge]

I believe it's that you are using MySQL 3 and SMF is optimised for version 4. The actual problem is in the search which would be consistant. I believe RC2 has an option in "Feature Settings and Options" to disable caching is results, so turn this off

[[Unknown]]

You may want to turn off the option to do "whole word search" under "Search" options.  It will perform better.

I also recommend upgrading to MySQL 4 if at all possible.

Code Select Expand

SELECT DISTINCT t.ID_TOPIC, t.ID_FIRST_MSG, t.numReplies, 0 AS is_subject
FROM interact_topics AS t, interact_messages AS m
WHERE t.ID_TOPIC = m.ID_TOPIC
        AND m.ID_BOARD IN (2, 3)
        AND m.body RLIKE '[[:<:]]install[[:>:]]';

Thinking out loud, I wonder if an index on both ID_BOARD and ID_TOPIC would help this query?  Probably not much, because we have body in there anyway...

-[Unknown]

[bob]

I did both of the suggestions and it seems like everything is ok for now. I've got other stuff on that mysql server so I'm not hurrying to install version 4 yet....