Speeding up MySQL SELECT queries with no results

Question

I have a MySQL table of restaurants, described like so:

 CREATE TABLE `restaurants` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) NOT NULL,
    `description` varchar(512) NOT NULL,
    `tags` varchar(512) DEFAULT NULL,
    `type` enum('A','D','E','L','Q','R','P','T','Z') NOT NULL,
    `popularity` int(11) DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uc_type_name` (`type`,`name`),
    KEY `name_idx` (`name`),
    KEY `type_popularity_idx` (`type`,`popularity`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Here is some status info about the table:

Name: restaurants
    Engine: MyISAM
    Version: 10
    Row_format: Dynamic
    Rows: 72999
    Avg_row_length: 84
    Data_length: 6204488
    Max_data_length: 281474976710655
    Index_length: 5280768
    Data_free: 0
    Auto_increment: 75634
    Collation: utf8_general_ci
    Checksum: NULL
    Create_options:
    Comment:

I am trying to build a query that returns the 6 most popular restaurants that match a given term for a given restaurant type:

SELECT `id`, `name`, `type`, `description` FROM `restaurants` 
WHERE `type` = 'A'
      AND (`name` LIKE '%some restaurant%'
           OR `description` LIKE '%some restaurant%' 
           OR `tags` LIKE '%some restaurant%')
ORDER BY `popularity` DESC
LIMIT 6

One of the restaurant types, A, contains 61,500 of the restaurants. Furthermore, these A type restaurants often have relatively long descriptions and names. Therefore, when there are no results for the lookup for type A, the queries take as long as 0.8-0.9 seconds. When there are results, however, they run as fast as 0.1 seconds.

How can I speed up the performance of this query?

score 1 · Answer 1 · answered Oct 29 '12 at 21:20

You can try using a fulltext index:

ALTER TABLE `restaurants` ADD FULLTEXT (`name`, `description`, `tags`);

Use MATCH:

SELECT `id`, `name`, `type`, `description` FROM `restaurants` 
    WHERE `type` = 'A'
        AND MATCH (`name`, `description`, `tags`) AGAINST ('some restaurant')
    ORDER BY `popularity` DESC LIMIT 6

Also, run

EXPLAIN SELECT `id`, `name`, `type`, `description` FROM `restaurants` 
    WHERE `type` = 'A'
        AND MATCH (`name`, `description`, `tags`) AGAINST ('some restaurant')
    ORDER BY `popularity` DESC LIMIT 6

to check if the indexes are used.

You can find out if it helps only by testing. Performance of LIKE and fulltext search can vary greatly depending on environment and table structure... At least it is not going to search the whole table.

Thank you. I had a FULLTEXT on the table originally, which was indeed very fast, however I was unable to mimick the behavior of LIKE '%some term%'. Not even in boolean mode unfortunately, as even then a search for "erflo" won't end up matching "Stack Overflow". How do I use the FULLTEXT index to search in this way? Thanks again! — user1094786, Oct 30 '12 at 19:55
Are you maybe looking for something like this SO question? http://stackoverflow.com/questions/634995/implementation-of-levenshtein-distance-for-mysql-fuzzy-search — DavidScherer, May 17 '13 at 01:58

Speeding up MySQL SELECT queries with no results

1 Answers1