MySQL: Could this be faster? (multiple LIKE)

evdoxos · 03-29-2008, 06:01 AM

Code:

// 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 · 03-29-2008, 06:28 AM

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 · 03-29-2008, 10:53 AM

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.

sdsinc · 03-29-2008, 01:10 PM

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

evdoxos · 03-29-2008, 01:35 PM

Originally Posted by Kath

... 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 · 03-29-2008, 03:09 PM

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 · 03-29-2008, 04:05 PM

Originally Posted by 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.
????: NamePros.com http://www.namepros.com/programming/450722-mysql-could-this-faster-multiple-like.html
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.

03-29-2008, 06:28 AM	#2 (permalink)
Brujah Senior Member Join Date: Apr 2006 Posts: 1,478	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 __________________ Free Keyword Popularity, CPC, etc... Namejet, Snapnames, GoDaddy Lists Offers valid for 24 hours. Now Available: Rented.com

03-29-2008, 01:10 PM	#4 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	IMO a regexp as suggested above could be what you are looking for. Indexing the fields would also improve performance. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists Last edited by Kath; 03-29-2008 at 01:11 PM. Reason: typo

03-29-2008, 03:09 PM	#6 (permalink)
Brujah Senior Member Join Date: Apr 2006 Posts: 1,478	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 __________________ Free Keyword Popularity, CPC, etc... Namejet, Snapnames, GoDaddy Lists Offers valid for 24 hours. Now Available: Rented.com

03-29-2008, 10:53 AM	THREAD STARTER #3 (permalink)
evdoxos NamePros Member Join Date: Mar 2008 Posts: 79	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)