5

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   ||
  • I must say I am surprised, I have replicated your setup with similar columns and rows in an Amazon micro instance (700Mb of memory, mechanical disks) and I am getting times of 0.01 seconds. Can you profile your query as instructed here stackoverflow.com/questions/11274892/… to try to identify external problems? MySQL Fulltext is limited, but not that bad. – jynus Jul 24 '14 at 13:56
  • I've added the profile. – user3841653 Jul 24 '14 at 15:50
  • Is this the InnoDB version or the MyISAM one? Do you get consistent results if you reexecute it several times? Which MySQL version and what kind of hardware (memory, disk) are you running it on? – jynus Jul 24 '14 at 16:25
  • Thanks for helping out. 1. It is MyISAM. You can check table create code. 2. Yes I get consistent results if i execute query multiple times. 3. MySQL 5.6.17. SSD HW, 8GB RAM, using a windows 8 machine with WAMP server. – user3841653 Jul 24 '14 at 16:34
2

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.