WHERE LIKE query and indices

[Anon]

I am doing a query like this on my mysql table:
select * from tblFile WHERE ClientID = 5 AND ProductID = 6 AND FileName LIKE '%4498.tif%';
I have created an index on ClientID, GroupID and FileName.

When I explain the query:

mysql> explain select * from tblFile WHERE ClientID = 5 AND ProductID = 6 AND FileName LIKE '%4498.tif%';
+---------+------+---------------------+---------------------+---------+-------------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------------+---------------------+---------+-------------+--------+------------+
| tblFile | ref | idx_tblFile_Search1 | idx_tblFile_Search1 | 8 | const,const | 302059 | where used |
+---------+------+---------------------+---------------------+---------+-------------+--------+------------+

It is searching too many rows, which makes my query very slow. How can I create a better index?

[sarahk]

The explain doesn't help me much but you should have the following columns indexed:
clientid
productid

GroupId is not referenced in this query so the index doesn't get used.

Do you regularly search for just part of a filename or is this an irregular event? Likes will always be slow and don't use the index.

If you could break up the name into the fragments always searched, say the file extension and put that in a separate field it might help. Given the limited range of file extensions there would be no benefit in indexing that column but it would help you avoid a "like"