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

Answer 3

MySQL is good for storing data but not great when it comes down to fast text based search.

Apart from Sphinx which has been already suggested I recommend two fantastic search engines:

1. Solr with http://pecl.php.net/package/solr - very popular search engine. Used on massive services like NetFlix.

2. Elastic Search - relatively new software but with very active community and lots of respect

Both solution are based on the same library Apache Lucene

Answer 4

Most of the search engine oriended sites are use FULL-TEXT-SEARCH. It will be very faster compare to select and LIKE... I have added one example and some links ... I think it will be useful for you... In this full text search have some conditions also...

STEP:1

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
);

STEP:2

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

STEP:3
Natural Language Full-Text Searches:

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('database');

Boolean Full-Text Searches

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

Go through this links viralpatel.net,devzone.zend.com,sqlmag.com,colorado.edu,en.wikipedia.org

Answer 5

If the "ORDER BY" is really the bottleneck, the straight-forward solution would be to remove the "ORDER BY" logic from your query, and re-implement the sorting directly in your application's code using C# sorting. Unfortunately, this means you'd also have to move your pagination into your application, since you'd need to obtain the complete result set before you can sort & paginate it. I'm just mentioning this because no-one else so far appears to have thought of it.

Frankly (like others have pointed out), the query you showed at the top should not need full-text indexing. A single suffix wildcard (e.g., LIKE 'ABC%') should be very effective as long as a BTREE (and not a HASH) index is available on the column in question.

And, personally, I have no aversion to even double-wildcard (e.g., LIKE '%ABC%"), which of course can never make use of indexes, as long as a full table scan is cheap. Probably 250,000 rows is the point where I'll start to seriously consider full-text indexing. 100,000 is definitely no problem.

I always make sure my SELECT's are dirty-reads, though (no transactionality applied to the select).

It's dirty once it gets to the user's eyeballs in any case!

Answer 6

It's so strange query :) Let's try to understand what it does.

The results are less than 30 rows from the table "businessunit" with some conditions.

The first condition is a foreign key of the "businessunit-postaddress" table.
Please check if you have an index on the column businessunit-postaddress.BusinessUnit.

The second one is a filter for returning rows only with businessunit.Name begining with 'tanto'.
If I didn't make a mistake you have a very complex index 'Business' consists of 11 fields!
And field 'Name' is not the first field in this index.
So this index is useless when you run "like tanto%"'s query.
I have strong doubt about necessity of this index at all.
By the way it demands quite big resources for its maintaining and slow down edit operations with this table.
You have to make an index with the only field 'Name'.

After filtering the query is sorting results and do it in some strange way too.
At first it sorts by field businessunit.Premium - it's normal.
However next statements with CASE are useless too.
That's why.
The zero are assigned to Name = 'tanto' (exactly).
The next rows with the one are rows with space after 'tanto' - these will be after 'tanto' in any cases (except special symbols) cause space is lower than any letter.
The next rows with the two are rows with some letters after 'tanto' (include space!). These rows will be in this order too by definition.
And the three is "reserved" for "other" rows but you won't get "other" rows - remeber about [WHERE businessunit.Name LIKE 'tanto%'] condition.
So this part of ORDER BY is meaningless.
And at the end of ORDER BY there is businessunit.Name again...

My advice: you need rebuild the query from scratch keeping in mind what you want to get.

Anyway I guess you can use

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,
`businessunit`.`Name`
LIMIT 0 , 30

Don't forget about an index on field businessunit-postaddress.BusinessUnit!

And I have strong assumption about field Premium. I guess it is designed for storing binary data (yes/no). So an ordinary (BTREE) index doesn't match. You have to use bitmap index.

P.S. I'm not sure that you really need to use SQL_CALC_FOUND_ROWS MySQL: Pagination - SQL_CALC_FOUND_ROWS vs COUNT()-Query

Answer 7

Its either full-text(http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) or the pattern matching (http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html) from php and mysql side.

From experience and theory:

Advantages of full-text -
1) Results are very relevant and de-limit characters like spacing in the search query does not hinder the search.
Disadvantages of full-text -
1) There are stopwords that are used as restrictions by webhosters to prevent excess load of data.(E.g. search results containing the word 'one' or 'moz' are not displayed. And this can be avoided if you're running your own server by keeping no stopwords.
2) If I type 'ree' it only displays words containing exactly 'ree' not 'three' or 'reed'.

Advantages of pattern matching -
1) It does not have any stopwords as in full-text and if you search for 'ree', it displays any word containing 'ree' like 'reed' or 'three' unlike fulltext where only the exact word is retreived.
Disadvantages of pattern matching-
1) If delimiters like spaces are used in your search words and if these spaces are not there in the results, because each word is separate from any delimiters, then it returns no result.

Answer 8

If the argument of LIKE doesn't begin with a wildchard character, like in your example, LIKE operator should be able to take advantage of indexes.

In this case, LIKE operator should perform better than LOCATE or LEFT, so I suspect that changing the condition like this could make things worse, but I still think it's worth trying (who knows?):

WHERE LOCATE('tanto', `businessunit`.`Name`)=1

or:

WHERE LEFT(`businessunit`.`Name`,5)='tanto'

I would also change your order by clause:

ORDER BY
  `businessunit`.`Premium` DESC ,
   CASE WHEN `businessunit`.`Name` LIKE 'tanto %' THEN 1
        WHEN `businessunit`.`Name` = 'tanto'      THEN 0
        ELSE 2 END,
   `businessunit`.`Name`

Name has to be LIKE 'tanto%' already, so you can skip a condition (CASE will never return value 3). Of course, make sure that Premium field is indexed.

Hope this helps.

Answer 9

I think you need to collect the keys only, sort them, then join last

SELECT A.*,B.* FROM
(
    SELECT * FROM (
        SELECT id BusinessUnit,Premium
            CASE
                WHEN Name = 'tanto'      THEN 0
                WHEN Name LIKE 'tanto %' THEN 1
                WHEN Name LIKE 'tanto%'  THEN 2
                ELSE 3
            END SortOrder
        FROM businessunit Name LIKE 'tanto%'
    ) AA ORDER BY Premium,SortOrder LIMIT 0,30
) A LEFT JOIN `businessunit-postaddress` B USING (BusinessUnit);

This will still generate a filesort.

You may want to consider preloading the needed keys in a separate table you can index.

CREATE TABLE BusinessKeys
(
    id int not null auto_increment,
    BusinessUnit int not null,
    Premium      int not null,
    SortOrder    int not null,
    PRIMARY KEY (id),
    KEY OrderIndex (Premuim,SortOrder,BusinessUnit)
);

Populate all keys that match

INSERT INTO BusinessKeys (BusinessUnit,Premuim,SortOrder)
SELECT id,Premium
    CASE
        WHEN Name = 'tanto'      THEN 0
        WHEN Name LIKE 'tanto %' THEN 1
        WHEN Name LIKE 'tanto%'  THEN 2
        ELSE 3
    END
FROM businessunit Name LIKE 'tanto%';

Then, to paginate, run LIMIT on the BusinessKeys only

SELECT A.*,B.*
FROM
    (
        SELECT FROM BusinessKeys
        ORDER BY Premium,SortOrder
        LIMIT 0,30
    ) BK
    LEFT JOIN businessunit A ON BK.BusinessUnit = A.id
    LEFT JOIN `businessunit-postaddress` B ON A.BusinessUnit = B.BusinessUnit
;

CAVEAT : I use LEFT JOIN instead of INNER JOIN because LEFT JOIN preserves the order of the keys from the left side of the query.

Answer 10

I've read the answer to use Sphinx to optimize the search. But regarding my experience I would advise a different solution. We used Sphinx for some years and had a few nasty problems with segmentation faults and corrupted indice. Perhaps Sphinx isn't as buggy as a few years before, but for a year now we are very happy with a different solution:

http://www.elasticsearch.org/

The great benefits:

• Scalability - you can simply add another server with nearly zero configuration. If you know mysql replication, you'll love this feature
• Speed - Even under heavy load you get good results in much less than a second
• Easy to learn - Only by knowing HTTP and JSON you can use it. If you are a Web-Developer, you feel like home
• Easy to install - it is useable without touching the configuration. You just need simple Java (no Tomcat or whatever) and a Firewall to block direct access from the public
• Good Javascript integration - even a phpMyAdmin-like Tool is a simple HTML-Page using Javascript: https://github.com/mobz/elasticsearch-head
• Good PHP Integration with https://github.com/ruflin/Elastica
• Good community support
• Good documentation (it is not eye friendly, but it covers nearly every function!)

If you need an additional storage solution, you can easily combine the search engine with http://couchdb.apache.org/