1

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company.

Table Size:
Data Length - 0.9GB
Index Length - 1.1GB

    SELECT c.id FROM basetable c 
    WHERE MATCH(c.categories, c.company) 
    AGAINST('+keyword1 +keyword2 +keyword3' IN BOOLEAN MODE)
    LIMIT 100

Query Results:
only 88 rows

Query Status:
Key_read_requests - 4414336
Key_reads - 1029
Key_write_requests - 12
Key_writes - 2
Handler_read_next - 89

Query Explain:
1 | SIMPLE | c | fulltext | categories_company | categories_company | 0 | | 1 | Using where

Mysql Global Variables:
key_buffer_size - 16GB
key_cache_age_threshold - 300
key_cache_block_size - 1024
key_cache_division_limit - 100
max_heap_table_size - 6gb
tmp_table_size - 6gb

Fulltext Variables:
ft_boolean_syntax - + -><()~*:""&|
ft_max_word_len - 20
ft_min_word_len - 2
ft_query_expansion_limit - 20
ft_stopword_file - none

Server:
Dual XEONS CPUs
32GB RAM
HD 15kRPM.

Note that not all queries are slow. Some are less than a second; it depends on the keywords used on Full Text search.

Do you have any thoughts on how to improve this?

  • Could you post the the EXPLAIN output for your query? – James L Jul 11 '13 at 8:40
  • Done. Please see above "Query Explain". – john Jul 11 '13 at 8:59
  • You state that some searches are slower than others. What are the relative frequencies (how many rows contain the words in the query) of words used in the slower searches compared to the faster queries? Also, you seem to have obfuscated your query. Do you have any other WHERE conditions within your query? Any JOINs? – redguy Jul 11 '13 at 13:45
  • Please post the output of SHOW GLOBAL VARIABLES LIKE 'ft%'; – RolandoMySQLDBA Jul 11 '13 at 14:00
  • @RolandoMySQLDBA I updated my post; see above. Thanks! – john Jul 12 '13 at 9:01

Your Answer

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

Browse other questions tagged or ask your own question.