MySQL Forums
Forum List  »  Newbie

slow SQL with computed ordering
Posted by: Hugo van der Sanden
Date: August 06, 2004 06:05AM

(Is this the right place for this question?)

I have a particularly slow query, and I'm not sure how to improve it without caching the calculated ordering priority in a table somewhere, with the attendant maintenance problems of ensuring that cached value is updated at the right times.

The context is a generic search tool which should return results in a particular order according to additional (configured) parameters. The query currently looks roughly like:

SELECT item.*, max(
case cma.classid
when ? then 4
else when ? then 3
else when ? then 2
else when ? then 1
else 0
) as _level
FROM item, classmember cma, classmember cmb
WHERE = classmember.itemid
AND ( = cmb.itemid and cmb.classid = ?)
AND (other user-supplied search criteria here ...)
ORDER BY _level desc,

.. where the classid values are bound at runtime from a dynamic configuration.

(Hmm, in preview that SQL is rather difficult to read: any way to get the whitespace turned into   for such things?)

The main problem is that the user can search for items simultaneously in multiple classes, and for each one I'm adding an extra join on classmember and an extra ( = cmxx.itemid and cmxx.classid = ?) test. With even one such class specified (as in the example SQL above) the explain looks like:

| table | type | possible_keys | key | key_len | ref | rows | Extra |
| cmb | range | PRIMARY | PRIMARY | 4 | NULL | 277 | where used; Using temporary; Using filesort |
| item | eq_ref | PRIMARY | PRIMARY | 4 | cmb.itemid | 1 | where used |
| cma | index | NULL | PRIMARY | 8 | NULL | 5561 | where used; Using index |

.. and depending on the actual search this bumps the time from about 0.1 seconds into the 10s of seconds. Any suggestions on how I might cut that down without massive surgery to the application would be welcomed.

(This is running under MySQL 3.23.57 driven by a large perl application linked with DBD::mysql v2.9003, on a dedicated database server on RedHat Linux 7.3.)



Options: ReplyQuote

Written By
slow SQL with computed ordering
August 06, 2004 06:05AM

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.