Hi Aaron (if that's your name) using LIKE with a '%' as the first character means MySQL cannot use the FileName index (since % means anything, the index is no use) - if you can, take out the first '%'
Also, you mention indexing GroupID, but talk about ProductID - is this a typo? If this doesn't help, let me know some more info - total rows, total rows where the various indexes apply, what kind of columns you're using, what version of MySQL you're using etc! I've also cc'd this to the mysql mailing list - it may be useful for someone there, and if you mail further questions there (and look at the excellent online manual), you're likely to get a quicker response - I'm normally not that punctual about answering email! regards, ian gilfillan > Hi there. 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 seraching too many rows, which makes my query very slow. How can I create a better index? --------------------------------------------- This message was sent using M-Web Airmail. JUST LIKE THAT Are you ready for 10-digit dialling on the 8th of May? To find out how this will affect your Internet connection go to www.mweb.co.za/ten http://airmail.mweb.co.za/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
