advanced search with mysql

Question

I'm creating a search function for my website where the user can put in anything he likes in a textfield. It get's matched against anything (name, title, job, car brand, ... you name it)

I initially wrote the query with an INNER JOIN on every table that needed to be searched.

SELECT column1, column2, ... FROM person INNER JOIN person_car ON ... INNER JOIN car ...

This ended up in a query with 6 or 8 INNER JOINs, and a whole lot WHERE ... LIKE '%searchvalue%'

Now this query seems to cause a time'out in MySql, and I even got a warning from my hosting provider that the queries just taking up too many resources.

Now obviously I'm doing this very wrong, but I was wondering how the correct approach to these kind of search functions is.

Thanks!

Answer 1

Use multiple queries or UNION multiple queries so they go into a single resultset.

Additionally, using FULLTEXT indexes will most likely help to speed up your queries since (LIKE '%string%') - especially with the leading '%' - is extremely slow (all rows need to be checked without using indexes)

Answer 2

I recommend implementing Lucene or Sphynx search engines, they are much faster and scalable than sql queries.