Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Could you post the the EXPLAIN output for your query? –  James 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
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.