Searching a big mysql database with relevance

Question

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

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

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

Answer 1

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.

Answer 2

Seems like the index doesn't cover Premium, yet that is the first ORDER BY argument.

Use EXPLAIN your query here to figure out the query plan and change your index to remove any table scans as explained in http://dev.mysql.com/doc/refman/5.0/en/using-explain.html