MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing this query slows it down
Posted by: wm
Date: February 28, 2007 08:33AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing this query slows it down
2933
wm
February 28, 2007 08:33AM
1901
February 28, 2007 06:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.