Help with select like '%word%' and indexes

[juancuca]

Hi !
English is my second language, I hope you can understand what I want to do.
Im using MySQL as a DB.
I have a table with over 3.000.000 rows. This rows have filenames information. I want to search within this rows and display the matching files. I've made a form where I insert the string to search, i.e. : 'word1 word2 word3 word4' and so on...

Im sending this string to another PHP file where I will 'explode' this string into an array : 'word1', 'word2', 'word3', 'word4'...

Then I will make a query =

select * from 'table' where 'filename' like '%word1%' and 'filename' like '%word2%' and 'filename' like '%word3%' and 'filename' like '%word4%'

My question is: Is there any way to improve the speed of my search ? The row 'filename' is indexed... but I've been told that when I search a string with like '% %' MySQL doesnt use indexes...

Any tips or any URL where I can read more about this ?
Just remember Im still kind of a noob :p

[ultraslacker]

Use EXPLAIN SELECT * FROM ... to see the query plan - that will show you that mysql will not be using indexes for that query. Mysql does not use indexes for LIKE expressions that begin with wildcards (see mysql manual), so it does a sequential scan.

You may want to try FULLTEXT index and use MATCH instead of LIKE - I would recommend using mysql 4.0.x for this.