Have Geek Brain

Advanced search with mysql

I am creating a search function for my site where the user can put whatever they like in a text box. It matches anything (name, title, job, car make, ... you name it)

I originally wrote a query with an INNER JOIN on each table that needed to be searched.

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

It ended up in a query with 6 or 8 INNER JOINs and an integer WHERE ... LIKE '% searchvalue%'

Now this request is causing a timeout in MySql and I even got a warning from my hosting provider that the requests are simply taking up too many resources.

Now, obviously I am doing it very wrongly, but I was wondering how to properly approach such search functionality.

+2


a source to share


2 answers


Use multiple queries or multiple UNION queries to be included in the same result set.



Also, using FULLTEXT indexes will likely help speed up your queries as (LIKE '% string%') - especially with a leading '%' - is very slow (all strings should be checked without using indexes)

+1


a source


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



0


a source






More articles:


All Articles