slow SQL with computed ordering

[Hugo van der Sanden]

(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
end
) as _level
FROM item, classmember cma, classmember cmb
WHERE item.id = classmember.itemid
AND (item.id = cmb.itemid and cmb.classid = ?)
AND (other user-supplied search criteria here ...)
GROUP BY item.id
ORDER BY _level desc, item.id

.. 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 (item.id = 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.)

Cheers,

Hugo