MySQL: Improve query time with word search

Question

Scenario

I have a search facility on a website that sells products.
The page is built with PHP and MySQL searching against a Merged View that joins 10+ tables together. There are approx 12,000 records in the view with 20+ fields each.

A user can search for products matching a specific criteria using multiple (10-15) select menus.
The database then returns results based on the select menu values against the products primary keys.

This all works fine and at a fast enough speed as it is.

Problem

However, on the same page, I have also included a textbox so users can manually type in what they are searching for rather than using the menus.

As the users are typing actual words rather than selecting PK's via a menu, the database has to do a word search. For lack of knowledge on a better method, I have concantenated all of the text values from the foreign criteria tables into one big field.
The textbox then searches against words within this concantenated field.

Again, this works fine, but changes a typical search time from 0.1 seconds to 2.0 seconds.
I have indexed all of the fields from the tables used by the Merged View in an effort to improve times, this however, didn't help at all.

Question

Since Google can find 22,200,000 pages in 1 second for the word "Overflow", and my database takes 2 seconds to search for a word against only 12,000 records.

How can I improve the layout and search method of the database?

Answer 1

You should consider keeping the words in memory, so that you do not have to hit the DB every single time you need a word search. Assembling your words into a data structure would work, and you could simply build the data structure at deploy-time from your db.

Answer 2

MySQL can do full-text search if you create a FULLTEXT index on one or more columns. From O'Reilly Databases:

FULLTEXT indices in MySQL allow database administrators and programmers to designate any character-based field (CHAR, VARCHAR, or TEXT) as a FULLTEXT index, which allows for complex text searching against data stored in those fields.

This feature is not to be confused with the LIKE function in MySQL. LIKE works more along the lines of a regular expression. On the other hand, FULLTEXT indices are fully indexed fields which support stopwords, boolean searches, and relevancy ratings.

Answer 3

I have concantenated all of the text values from the foreign criteria tables into one big field. The textbox then searches against words within this concantenated field.

This makes no sense to me. Have you tried just using a WHERE LIKE on the appropriate fields? Am I missing something?

Answer 4

I would highly recommend Lucene Solr in this situation. It has a reverse index system that is very well-suited to this task. You can use it with PHP (you interface with it via frontend).

Take a crack at it (at least look at some of the tutorials), and you'll be amazed at how quickly you are up and running.