Mysql 5.7 Fulltext search sometimes very slow
Posted by: lisheng1 zhang1
Date: March 21, 2017 02:07PM
Date: March 21, 2017 02:07PM
we have been using mySQL fulltext search for sometime and recently upgraded from 5.5 to 5.7 (using innoDB fulltext search now), it works well feature wise, thanks a lot!
we noticed each morning the 1st fulltext search is usually rather slow (50s), if we repeat same SQL (even with SQL_NO_CACHE) it would be much faster (1-2s), we really need to overcome the initial slowness, your guidance would be really appreciated.
Main Table (which has about 1.2M records):
CREATE TABLE `OBJECT_SEARCH` (
`ID` bigint(20) unsigned NOT NULL,
`DATA` longtext,
`TITLE_DATA` longtext,
`APPEN_DATA` longtext,
PRIMARY KEY (`ID`),
FULLTEXT KEY `SEARCH_IDX` (`DATA`),
FULLTEXT KEY `SEARCH_TITLE_IDX` (`TITLE_DATA`),
FULLTEXT KEY `SEARCH_APPEN_IDX` (`APPEN_DATA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
FT variables (for our app we need to index all common words):
innodb_ft_aux_table | |
innodb_ft_cache_size | 8000000 |
innodb_ft_enable_diag_print | OFF |
innodb_ft_enable_stopword | OFF |
innodb_ft_max_token_size | 84 |
innodb_ft_min_token_size | 1 |
innodb_ft_num_word_optimize | 2000 |
innodb_ft_result_cache_limit | 2000000000 |
innodb_ft_server_stopword_table | |
innodb_ft_sort_pll_degree | 2 |
innodb_ft_total_cache_size | 640000000 |
innodb_ft_user_stopword_table | |
SQL (we need to join a few tables to limit the search scope, without those constraints SQL would be much slower):
SELECT DISTINCT b.ID,b.ACTIVITY_TIME FROM OBJECT_SEARCH AS c straight_join CV_13760_OBJECT_DATA AS b on ((b.ID) = (c.ID)) inner join CV_13760_OBJECTS AS a on ((b.ID) = (a.ID)) WHERE (((((((((((((((((((((((((b.TYPE) = '27') OR (((b.TYPE) = '29') AND ((b.SUBTYPE) IN ('20')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) NOT IN ('23')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR ((b.TYPE) = '2')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('5')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('0')))) OR ((b.TYPE) = '38')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('3')))) OR ((b.TYPE) = '22')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR ((b.TYPE) = '4')) OR ((b.TYPE) = '4')) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('0')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) AND ((match(c.TITLE_DATA) against(' ( +vmoso)' in Boolean Mode) OR match(c.DATA) against(' ( +vmoso)' in Boolean Mode)) OR match(c.APPEN_DATA) against(' ( +vmoso)' in Boolean Mode)) AND ((b.STATUS) IN ('1')) ORDER BY (b.ACTIVITY_TIME) DESC , (b.ID) DESC LIMIT 0,11;
Query plan:
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | a | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 39391 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | vmoso_db.a.ID | 1 | 29.76 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY,OBJECT_TYPE_INDEX,OBJECT_LIFECYCLE_INDEX | PRIMARY | 8 | vmoso_db.a.ID | 1 | 9.05 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
In query plan i did not fulltext index is mentioned (without using fulltext index how could above SQL get executed?)
Thanks very much for helps
we noticed each morning the 1st fulltext search is usually rather slow (50s), if we repeat same SQL (even with SQL_NO_CACHE) it would be much faster (1-2s), we really need to overcome the initial slowness, your guidance would be really appreciated.
Main Table (which has about 1.2M records):
CREATE TABLE `OBJECT_SEARCH` (
`ID` bigint(20) unsigned NOT NULL,
`DATA` longtext,
`TITLE_DATA` longtext,
`APPEN_DATA` longtext,
PRIMARY KEY (`ID`),
FULLTEXT KEY `SEARCH_IDX` (`DATA`),
FULLTEXT KEY `SEARCH_TITLE_IDX` (`TITLE_DATA`),
FULLTEXT KEY `SEARCH_APPEN_IDX` (`APPEN_DATA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
FT variables (for our app we need to index all common words):
innodb_ft_aux_table | |
innodb_ft_cache_size | 8000000 |
innodb_ft_enable_diag_print | OFF |
innodb_ft_enable_stopword | OFF |
innodb_ft_max_token_size | 84 |
innodb_ft_min_token_size | 1 |
innodb_ft_num_word_optimize | 2000 |
innodb_ft_result_cache_limit | 2000000000 |
innodb_ft_server_stopword_table | |
innodb_ft_sort_pll_degree | 2 |
innodb_ft_total_cache_size | 640000000 |
innodb_ft_user_stopword_table | |
SQL (we need to join a few tables to limit the search scope, without those constraints SQL would be much slower):
SELECT DISTINCT b.ID,b.ACTIVITY_TIME FROM OBJECT_SEARCH AS c straight_join CV_13760_OBJECT_DATA AS b on ((b.ID) = (c.ID)) inner join CV_13760_OBJECTS AS a on ((b.ID) = (a.ID)) WHERE (((((((((((((((((((((((((b.TYPE) = '27') OR (((b.TYPE) = '29') AND ((b.SUBTYPE) IN ('20')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) NOT IN ('23')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR ((b.TYPE) = '2')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('5')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '16') AND ((b.SUBTYPE) IN ('18')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('0')))) OR ((b.TYPE) = '38')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('3')))) OR ((b.TYPE) = '22')) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR ((b.TYPE) = '4')) OR ((b.TYPE) = '4')) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('0')))) OR (((b.TYPE) = '2') AND ((b.SUBTYPE) IN ('7')))) OR (((b.TYPE) = '22') AND ((b.SUBTYPE) IN ('1')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '11') AND (((b.LIFECYCLE_STATE) = '1') OR ((b.LIFECYCLE_STATE) = '0')))) OR (((b.TYPE) = '20') AND ((b.SUBTYPE) IN ('23')))) AND ((match(c.TITLE_DATA) against(' ( +vmoso)' in Boolean Mode) OR match(c.DATA) against(' ( +vmoso)' in Boolean Mode)) OR match(c.APPEN_DATA) against(' ( +vmoso)' in Boolean Mode)) AND ((b.STATUS) IN ('1')) ORDER BY (b.ACTIVITY_TIME) DESC , (b.ID) DESC LIMIT 0,11;
Query plan:
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | a | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 39391 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 8 | vmoso_db.a.ID | 1 | 29.76 | Using where |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY,OBJECT_TYPE_INDEX,OBJECT_LIFECYCLE_INDEX | PRIMARY | 8 | vmoso_db.a.ID | 1 | 9.05 | Using where |
+----+-------------+-------+------------+--------+--------------------------------------------------+---------+---------+---------------+-------+----------+----------------------------------------------+
In query plan i did not fulltext index is mentioned (without using fulltext index how could above SQL get executed?)
Thanks very much for helps
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.