MySQL: Could this be faster? (multiple LIKE)

[evdoxos]

// searching for: at least one of (key1, key2, key3) in 
  // at least one of (field1, field2, field3)                                                                  
  SELECT * FROM table_name WHERE (field1 LIKE '%key1%'
                                    OR field1 LIKE '%key2%'
                                    OR field1 LIKE '%key3%'
                                    // other field
                                    OR field2 LIKE '%key1%'
                                    OR field2 LIKE '%key2%'
                                    OR field2 LIKE '%key3%'
                                    // other field
                                    OR field3 LIKE '%key1%'
                                    OR field3 LIKE '%key2%'
                                    OR field3 LIKE '%key3%');
  
  // searching for: everyone of (key1, key2, key3) in 
  // everyone of: (field1, field2, field3)                                  
  SELECT * FROM table_name WHERE (field1 LIKE '%key1%'
                                    AND field1 LIKE '%key2%'
                                    AND field1 LIKE '%key3%'
                                    // other field
                                    AND field2 LIKE '%key1%'
                                    AND field2 LIKE '%key2%'
                                    AND field2 LIKE '%key3%'
                                    // other field
                                    AND field3 LIKE '%key1%'
                                    AND field3 LIKE '%key2%'
                                    AND field3 LIKE '%key3%');
                                    
  // searching for: none of (key1, key2, key3) in 
  // none of: (field1, field2, field3)                                  
  SELECT * FROM table_name WHERE (field1 NOT LIKE '%key1%'
                                    AND field1 NOT LIKE '%key2%'
                                    AND field1 NOT LIKE '%key3%'
                                    // other field
                                    AND field2 NOT LIKE '%key1%'
                                    AND field2 NOT LIKE '%key2%'
                                    AND field2 NOT LIKE '%key3%'
                                    // other field
                                    AND field3 NOT LIKE '%key1%'
                                    AND field3 NOT LIKE '%key2%'
                                    AND field3 NOT LIKE '%key3%');

Could any of above queries be faster?

 

[Brujah]

I'm not sure if it could be faster, but you can 'EXPLAIN' the queries to see which of them are using indexes and which are not.
http://dev.mysql.com/doc/refman/5.0/en/explain.html

You might consider using REGEXP too.
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

 

[evdoxos]

All fields are VARCHAR or TEXT and none of them uses indexes (FULLTEXT) because I want exactly and all the results from a database (that it could be -from time to time- not very-very big).
Using FULLTEXT in a case like that, it could return no results although keys are in the fields.

 

[Kate]

IMO a regexp as suggested above could be what you are looking for. Indexing the fields would also improve performance.

 

[evdoxos]

... Indexing the fields would also improve performance.

Indexing the fields will not return all results, and I want all of them.
By this meaning indexes will not work at all if my database sometime stops to be huge. Am I wrong? I don't think so.

 

[Brujah]

You can index the fields, without using FULLTEXT for your searches. If I recall, you can also use a program called Sphinx too that may be what you need.
http://www.sphinxsearch.com/

Looking at the features, it may be exactly what you need for performance you desire.
http://www.sphinxsearch.com/features.html

 

[evdoxos]

You can index the fields, without using FULLTEXT for your searches. If I recall, you can also use a program called Sphinx too that may be what you need.
http://www.sphinxsearch.com/

Looking at the features, it may be exactly what you need for performance you desire.
http://www.sphinxsearch.com/features.html

Thank you Brujah, but currently I'm working on my master’s degree and I can not use ready solutions.