Search code examples
phpmysqlsearchleft-joininner-join

Searching a big mysql database with relevance

I'm building a rather large "search" engine for our company intranet, it has 1miljon plus entries it's running on a rather fast server and yet it takes up to 1 min for some search queries.

This is how the table looks

Table

I tried create an index for it, but it seems as if i'm missing something, this is how the show index is showing

idexes

and this is the query itself, it is the ordering that slows the query mostly but even a query without the sorting is somewhat slow.

SELECT SQL_CALC_FOUND_ROWS *
FROM `businessunit`
INNER JOIN `businessunit-postaddress` ON `businessunit`.`Id` = `businessunit-postaddress`.`BusinessUnit`
WHERE `businessunit`.`Name` LIKE 'tanto%'
ORDER BY `businessunit`.`Premium` DESC ,
CASE WHEN `businessunit`.`Name` = 'tanto'
THEN 0
WHEN `businessunit`.`Name` LIKE 'tanto %'
THEN 1
WHEN `businessunit`.`Name` LIKE 'tanto%'
THEN 2
ELSE 3
END , `businessunit`.`Name`
LIMIT 0 , 30

any help is very much appreciated

Edit: What's choking this query 99% is ordering by relevance with the wildcharacter % When i Do an explain it says using where; using fsort

Solution

  • You should try sphinx search solution which is full-text search engine will give you very good performance along with lots of options to set relevancy.

    Click here for more details.