MySQL fulltext selection performance

Question

I have 1.5M rows in a table. Following is the table create code:

CREATE TABLE `jobs` (
    `id` INT(8) NOT NULL AUTO_INCREMENT,
    `job_id` VARCHAR(50) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `company` VARCHAR(255) NOT NULL DEFAULT '',
    `city` VARCHAR(50) NOT NULL DEFAULT '',
    `state` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `job_id` (`job_id`),
    FULLTEXT INDEX `search` (`title`, `company`, `city`, `state`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

The query below takes about 0.3 seconds, which is very high.

SELECT id 
     , title 
     , company 
     , state 
     , city 
FROM `jobs` 
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')  
LIMIT 0,10

How can I decrease execution time and still provide relevant results? Any suggestions?

So far I tried followings but there is no improvement at all.

• Searching in a single field that contains 4 field of data but it did not matter.

• Using in Boolean mode>1 or >2, but then it gives me unrelated results

• Repairing the table, increasing key_buffer_size to 1GB from 16MB, changing table type to Innodb, changing character set to latin1 from utf8.

• Setting ft_max_word_len=1 and ft_stopword_file='' from default values.

• I searched online for many hours but no luck so far.

"Explain select..." output:

id;select_type;table;type    ;possible_keys;key   ;key_len;ref;rows;Extra
1 ;SIMPLE     ;jobs ;fulltext;search       ;search;0      ;\N ;1   ;Using where

EDIT: SHOW PROFILE OUTPUT:

|| *Status*                || *Duration* ||
|| starting                || 0.000087   ||
|| checking permissions    || 0.000011   ||
|| Opening tables          || 0.000036   ||
|| init                    || 0.000030   ||
|| System lock             || 0.000013   ||
|| optimizing              || 0.000010   ||
|| statistics              || 0.000021   ||
|| preparing               || 0.000008   ||
|| FULLTEXT initialization || 0.276820   ||
|| executing               || 0.000010   ||
|| Sending data            || 0.000155   ||
|| end                     || 0.000008   ||
|| query end               || 0.000004   ||
|| closing tables          || 0.000016   ||
|| freeing items           || 0.000703   ||
|| cleaning up             || 0.000018   ||

Answer 1

I just answered this recent question : Full text search results in a large amount of time spent in 'FULLTEXT initialization'

Since you are using MyISAM, I would recommend refactoring your query

Here is my proposed query

SELECT id, 
title, 
company, 
state, 
city 
FROM `jobs` 
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')  
LIMIT 0,10


SELECT B.id, 
B.title, 
B.company, 
B.state, 
B.city 
FROM
(SELECT id FROM`jobs` 
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')  
LIMIT 0,10) A
LEFT JOIN `jobs` USING (id);

GIVE IT A TRY !!!

Answer 2

Not sure if this could be the problem, but you are defining the index:

FULLTEXT INDEX `search` (`title`, `company`, `city`, `state`)

and then asking with other order:

WHERE MATCH (title, company, state, city) AGAINST...

That is wrong, as you should maintain the order you stated in the index definition.