Description:
This looks similar to bug 1274, but not quite.
I have a rather large table and a search query ran against it. Running on mysql 4.0.14 the query would
run great, running in 0.1s against a 600k row table. Upgrading to 4.0.16 resulted in the performance
going down to taking 10-20s for the same query to complete. The two are on the same OS, using the
same files, with no changes (though I have run analyse table and various myisamchk functions against
the tables, with no changes in speed).
Looking into explain I found this on the old (fast) table:
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
| t1 | range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex8 | 4 |
NULL | 19645 | Using where |
| t2 | eq_ref | indx1 | indx1 | 100 | t1.B_Board | 1 |
|
| t3 | ref | PRIMARY,indx3 | indx3 | 4 | t1.B_PosterId | 1 |
|
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
and on the new (slow) table:
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+-----------------------------+
| t1 | ref | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx | w3t_Postsindex7 | 3 |
const | 6607 | Using where; Using filesort |
| t2 | eq_ref | indx1 | indx1 | 100 | t1.B_Board | 1 |
|
| t3 | eq_ref | PRIMARY,indx3 | PRIMARY | 4 | t1.B_PosterId | 1 |
|
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+-----------------------------+
In case the formatting doesn't come out, the latter is using a different index than the former, causing it
to go into "Using filesort". Details on my blog at http://arcterex.net/blog/archives/2004/02/04/
mysql_and_performance_problems.html
I'm guessing that in between versions mysql changed the way it selected the index to use, but the hit in
performance is a rough thing to have.
How to repeat:
The exact query being run is
SELECT
t1.B_Number,t1.B_Main,t3.U_Username,t1.B_Subject,t1.B_Posted,t1.B_Board,t3.U_Color,t1.B_Reged,t1.B_
Icon,t2.Bo_Title,t2.Bo_Read_Perm,t2.Bo_Cat,t1.B_Posterid,t1.B_Status,t1.B_AnonName,t1.B_Body
FROM w3t_Posts AS t1, w3t_Boards AS t2, w3t_Users AS t3 WHERE
t1.B_Approved = 'yes' AND t1.B_Board = t2.Bo_Keyword AND t1.B_Posterid = t3.U_Number
AND t1.B_Status <> 'M' AND t1.B_Board IN
('UBB1','UBB2','UBB3','UBB4','UBB5','UBB6','UBB7','UBB8','UBB9','UBB10','UBB11','UBB13','UBB14','UBB16','UB
B17','UBB18','UBB19','UBB20','UBB21','UBB22','UBB23','UBB24','UBB25','UBB26','UBB28','UBB29','UBB30','UB
B31','UBB32','UBB33','UBB34','UBB35','UBB37','UBB38','UBB39','UBB40','UBB41','UBB42','UBB43','UBB44','Ge
nAuto','RoadTrips','TDIFest2004','sightings','PDTDI','vdgbsale') AND t1.B_Posted >
'1074624594' AND ( (t1.B_Subject LIKE '%this%' OR t1.B_Body LIKE '%this%') OR (t1.B_Subject
LIKE '%or%' OR t1.B_Body LIKE '%or%') OR (t1.B_Subject LIKE '%that%' OR t1.B_Body LIKE '%that%') OR
(t1.B_Subject LIKE '%or%' OR t1.B_Body LIKE '%or%') OR (t1.B_Subject LIKE '%the%' OR t1.B_Body LIKE
'%the%') OR (t1.B_Subject LIKE '%other%' OR t1.B_Body LIKE '%other%') OR (t1.B_Subject LIKE '%thing%'
OR t1.B_Body LIKE '%thing%'))ORDER BY B_Posted DESC LIMIT 26;
Which of course isn't much use without the data. I don't have much control over the query, as it's
created by the UBBThreads forum software.
