MySQL fulltext selection performance

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

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 !!!

StackExchange Database Administrators Q#72188, answer score: 2