1

Current Query:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE '%base%' OR Title LIKE '%ball%')

The above query will take 0.0300 seconds approx.

Faster Query:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE 'base%' OR Title LIKE 'ball%')

The above query will take 0.0010 seconds approx.

The problem:

I need to the results from the first query, as the second (faster) query does not have the wildcard at the start (which allows for indexes to be used).

What options do I have?

One option is to have a separate table which contains all the keywords from the Title field, however this is not appropriate as I am looking to search on a character by character basis, not on a keyword by keyword basis.

For example, the following query should also work:

SELECT * FROM 'fnx_sports' WHERE (Title LIKE 'b%' OR Title LIKE 'b%' OR TitleReversed LIKE 'b%' OR TitleReversed LIKE 'b%')

I'm not sure if this is the most efficient way to do this is. I don't want to modify my table and code structure without confirming the best method. I appreciate any advice!

Note: Cannot use full-text since I have a table which gets quite large and needs constant inserts and selects, therefore I use INNODB instead of MYISAM which is required to use Full Text searches.

Joe
  • 415
  • 4
  • 17
  • 2
    It's time to look at [MySQL Full Text](http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html). *It does have some limitations..(excludes short words etc)* – RichardTheKiwi Mar 24 '11 at 11:22
  • 1
    You might want to investigate full-text searches. See http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html – Bob Jarvis - Слава Україні Mar 24 '11 at 11:24
  • Cannot use full-text since I have a table which gets quite large and needs constant inserts and selects, therefore I use INNODB instead of MYISAM which is required to use Full Text searches. – Joe Mar 24 '11 at 11:34
  • Then your only option is to use an external search engine like Sphinx or Apache Lucene. – acme Mar 24 '11 at 11:56
  • I will be using the slower query for now, but I will also be looking into this external search engine that has been mentioned. – Joe Mar 24 '11 at 12:19

3 Answers3

2

Maybe look at Lucene and Sphinx.

See:

How much more performant is Postgres than MYSQL on fulltext search?

(especially peufeu's answer)

Community
  • 1
  • 1
Maxime Pacary
  • 22,336
  • 11
  • 85
  • 113
1

As you are using InnoDB and the option to switch to an external search engine (which is the best and fastest option) like Lucene or Sphinx was already mentionend here a different approach:

If the results don't need to show up immediately in your search you could run a cronjob periodically (e.g. once per hour) that inserts new rows in the InnoDB table into a separate table 'searchindex' which is MyISAM with fulltext index and is being used only for searching.

acme
  • 14,654
  • 7
  • 75
  • 109
  • This is a clever idea, however I do not think it is appropriate in my case. Thanks though. – Joe Mar 24 '11 at 12:18
0

Are you always searching for whole words or at least the start of word?

If so you you could break up the words into a seperate table so "Base Ball Mania" would have three records in the table "Base", Ball", and "Mania", which could be indexed and searched.

Obviously this won't work if you are searching for "ball" and there is an entry for "baseball".

Jaydee
  • 4,138
  • 1
  • 19
  • 20
  • Yes, that wouldn't work for what I need. Otherwise I would search based on keywords but instead I am searching based on characters contained in the string. – Joe Mar 24 '11 at 12:18
  • Will there be particular searches that will be run more often, that could be cached? Or are the searches too general? – Jaydee Mar 24 '11 at 12:27