I've got a query that will occasionally take longer than 10 minutes to execute:
select distinct i.*
from items i, creators c, itemCreators ic
where (i.item_title like '%$input%'
or (c.ident_name like '%$input%'
and (c.cid = ic.cid and ic.cdid = i.cdid)))
order by i.item_title;
I'm giving this query, with a value in the place of $input != '' or '%', over to the db via php/pear. If I input a longer value, for instance 'ophelia', the query is fast enough and gives back the intended items; but if I input one character only (for instance 'o') it just takes forever to execute. I can imagine why this would be; but I'm not certain if there's a way I could optimize this?
I've gone through
http://dev.mysql.com/doc/mysql/en/mysql-optimization.html in the docs to an extent but nothing I've done is effective in this case. The fields i.item_title and c.ident_name are variable length fields (varchar), and each table i and c includes one text field, too.
Installed mysql is version 3.23.58 on fedora core 1.