Description:
My table consist of 600K records. The below queries have been troubling me for a while.
Query1: Below fulltext query takes "0.43 sec" for word "java" :
select count(*)
from icrd_resumebank
where match(`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,
`abilityKeywords`,`lngKeywords`,`roleKeywords`,
`industryKeywords`,`educationKeywords`,`prefLocKeywords`)
against ('java' in boolean mode);
Result: 168944 records
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away
Query2:
select count(*) from icrd_resumebank where sellercode=429;
Response time: 0.18 sec , Result : 845 records
+----+-------------+-----------------+------------+------+-------------------------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+-------------------------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | icrd_resumebank | NULL | ref | idx_s_p_m_e,idx_s_f_l_c_s,sel | sel | 9 | const | 845 | 100.00 | Using index
Query3: Combine fulltext with integer field takes more then 45 sec+.
select count(*)
from icrd_resumebank
where match(`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,
`abilityKeywords`,`lngKeywords`,`roleKeywords`,
`industryKeywords`,`educationKeywords`,`prefLocKeywords`)
against ('java' in boolean mode)
and sellercode=429;
Response time: 40.12 sec, Result : 452 records
-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+----------+--------------------------------------+--------+---------+-------+------+----------+-----------------------------------+
| 1 | SIMPLE | icrd_resumebank | NULL | fulltext | idx_s_p_m_e,idx_s_f_l_c_s,sel,fx_cat | fx_cat | 0 | const | 1 | 5.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------------+----
None of the combinations of the query are working. How to improve performance of the query of fulltext with integer fields?
Using MySQL version: 5.7
Storage Engine: InnoDB
How to repeat:
Fulltext and integer combined condition not utilizing the index.
1. KEY `sel` (`sellerCode`),
2. FULLTEXT KEY `fx_cat` (`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,`abilityKeywords`,`lngKeywords`,`roleKeywords`,`industryKeywords`,`educationKeywords`,`prefLocKeywords`)
Description: My table consist of 600K records. The below queries have been troubling me for a while. Query1: Below fulltext query takes "0.43 sec" for word "java" : select count(*) from icrd_resumebank where match(`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`, `abilityKeywords`,`lngKeywords`,`roleKeywords`, `industryKeywords`,`educationKeywords`,`prefLocKeywords`) against ('java' in boolean mode); Result: 168944 records id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away Query2: select count(*) from icrd_resumebank where sellercode=429; Response time: 0.18 sec , Result : 845 records +----+-------------+-----------------+------------+------+-------------------------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+------+-------------------------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | icrd_resumebank | NULL | ref | idx_s_p_m_e,idx_s_f_l_c_s,sel | sel | 9 | const | 845 | 100.00 | Using index Query3: Combine fulltext with integer field takes more then 45 sec+. select count(*) from icrd_resumebank where match(`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`, `abilityKeywords`,`lngKeywords`,`roleKeywords`, `industryKeywords`,`educationKeywords`,`prefLocKeywords`) against ('java' in boolean mode) and sellercode=429; Response time: 40.12 sec, Result : 452 records -----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+----------+--------------------------------------+--------+---------+-------+------+----------+-----------------------------------+ | 1 | SIMPLE | icrd_resumebank | NULL | fulltext | idx_s_p_m_e,idx_s_f_l_c_s,sel,fx_cat | fx_cat | 0 | const | 1 | 5.00 | Using where; Ft_hints: no_ranking | +----+-------------+-----------------+---- None of the combinations of the query are working. How to improve performance of the query of fulltext with integer fields? Using MySQL version: 5.7 Storage Engine: InnoDB How to repeat: Fulltext and integer combined condition not utilizing the index. 1. KEY `sel` (`sellerCode`), 2. FULLTEXT KEY `fx_cat` (`skillKeywords`,`anyKeywords`,`cvKeywords`,`infoKeywords`,`abilityKeywords`,`lngKeywords`,`roleKeywords`,`industryKeywords`,`educationKeywords`,`prefLocKeywords`)