Optimizing this query slows it down

[wm]

Hi there,

I have recently started using 'describe select' to see how I can improve things and usually adding an index or slightly changing the SQL helps greatly..

But not this bizzar case.

Its a join query using the slightly esoteric RLIKE and Im joining one table to itself.

In my existing functional query it takes 0.85 seconds on average.

Adding indexes to the 'switch' and 'class' fields did nothing.. still 0.85 seconds and no indication keys were being used.

I figured it was the final OR in the WHERE that was causing it to ignore keys and scan ALL records so I removed it.. and sure enough the 'describe' shows that keys were being used and a smaller number of records checked.. however it took a whopping 15.52 seconds!

Here are the 2 queries and their respective 'describe' results.

Any thoughts, suggestions or comments are greatly welcomed.

PS MySQL v4.0.15-nt (MyISAM tables) is running entirely in a 32Mb virtual RAM drive (yes you can get them for WinXP)

PPS Running the same application using MySQL 5 is very slow in comparison.

Thanks,

describe 
select o.obj_id, m.code, o.loc, m.obj_id 
from objects as o 
left join objects as m 
on concat('_',m.name,'_') rlike o.material 
where m.class='material' 
and m.code<>'' 
and  o.switch > 0 
and  ( o.loc > 0  
       and 
       ( m.code like '%##wakes:%' 
         or 
         m.code like '%if reacting to wakes then%'
       )
     )
or ( o.loc=0 and  m.code like '%##any:%' )  
order by o.switch desc


+-------+------+---------------+--------+---------+--------+-------+----------------------------------------------+
| table | type | possible_keys | key    | key_len | ref    | rows  | Extra                                        |
+-------+------+---------------+--------+---------+--------+-------+----------------------------------------------+
| o     | ALL  | switch        | [NULL] |  [NULL] | [NULL] | 10693 | Using where; Using temporary; Using filesort |
| m     | ALL  | class         | [NULL] |  [NULL] | [NULL] | 10693 | Using where                                  |
+-------+------+---------------+--------+---------+--------+-------+----------------------------------------------+

Running this query returns: 1 row in set (0.81 sec)

Now lets do the same SQL without the final OR:

describe 
select o.obj_id, m.code, o.loc, m.obj_id 
from objects as o 
left join objects as m 
on concat('_',m.name,'_') rlike o.material 
where m.class='material' 
and m.code<>'' 
and  o.switch > 0 
and  ( o.loc > 0  
       and 
       ( m.code like '%##wakes:%' 
         or 
         m.code like '%if reacting to wakes then%'
       )
     )
order by o.switch desc


+-------+-------+---------------+--------+---------+--------+-------+-------------+
| table | type  | possible_keys | key    | key_len | ref    | rows  | Extra       |
+-------+-------+---------------+--------+---------+--------+-------+-------------+
| o     | range | switch        | switch |       4 | [NULL] | 10127 | Using where |
| m     | ref   | class         | class  |     100 | const  |    74 | Using where |
+-------+-------+---------------+--------+---------+--------+-------+-------------+

Running this query returns: 1 row in set (15.52 sec)

http://creativeobjectworld.com :: a free-form, text-based, browser-based, real-time, multi-user virtual world