Interference of full-text index on index selection

Mysql full-text indexes can be used to improve performance. However, if they are not used, they will be an exception and a disaster. mysql full-text indexes affect the selection of indexes in the optimizer. See the one optimized in my production environment.

Mysql full-text indexes can be used to improve performance. However, if they are not used, they will be an exception and a disaster. mysql full-text indexes affect the selection of indexes in the optimizer. See the one optimized in my production environment.

Mysql full-text indexes can be used to improve performance. However, if they are not used, they will be an exception and a disaster. mysql full-text indexes affect the selection of indexes in the optimizer. Check an optimized SQL statement in my production environment.

Select distinct pc. products_id, pd. products_name, p. products_date_added, pso. products_id FROM products_to_categories AS pc left join products_description AS pd ON pd. products_id = pc. products_id left join products AS p ON p. products_id = pd. products_id left join specials AS sps ON sps. products_id = p. products_id left join temp_products_7days_orders_amount AS 7 days ON 7days. products_id = pc. products_id LEFT J OIN products_realtime_quantity AS prq ON prq. sku_or_poa = p. products_model left join products_stockout AS pso ON pso. products_id = pd. products_idWHERE p. products_status = 1 AND (prq. msg! = 'Temporary out stock. 'or isnull (prq. msg) AND pc. categories_id IN (153,323,105, 1431,) and match (pd. products_name) AGAINST ('* iphone *' in boolean mode) and match (pd. products_name) AGAINST ('* c *' in boolean mode) order by 7days. orders_sum DESC

The execution of this statement is very slow and often gets stuck. Sometimes it takes several minutes to execute and only a few results. This statement also involves large result operations, indexes are available on various join table conditions. The only special feature is that pd. products_name is the full-text index, and pc. categories_id has a higher priority than pd. products_name during execution, resulting in pc. categories_id index. It does not matter. However, the problem was found after explaining

+ ---- + ------------- + ------- + ---------- + Hour + ------------------- + --------- + hour + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + ---------- + ----------------------- + ------------------- + --------- + hour + ------ + hour + | 1 | SIMPLE | pc | range | PRIMARY, categories_id | 4 | NULL | 307 | Using where; Using temporary; Using filesort | 1 | SIMPLE | pd | fulltext | PRIMARY, products_name | 0 | 1 | Using where | 1 | SIMPLE | p | eq_ref | PRIMARY | 4 | banggood. pd. products_id | 1 | Using where | 1 | SIMPLE | sps | ref | products_id | 4 | banggood. pd. products_id | 16 | Using index | 1 | SIMPLE | 7 days | ref | PRIMARY | 4 | banggood. p. products_id | 1032 | 1 | SIMPLE | prq | ref | ix_prg_sku_or_poa | 152 | banggood. p. products_model | 10 | Using where | 1 | SIMPLE | pso | eq_ref | PRIMARY | 4 | banggood. pd. products_id | 1 | Using index | + ---- + --------------- + ------- + ---------- + --------------------- + ------------------- + ----------------------------- + ------ + accept +

We found that the driver represents the pc table, and the categories_id index is used. Maybe the optimizer selects it first, but then we can look at the pd table again,

In this case, the pd table should use the products_id index, which is the primary key of the table. However, the optimizer selects the full-text index of products_name!

The execution time of the profiling statement is more than 2 minutes.

+ Duration + ------------ + ----------- + ------------ + Duration + | Status | Duration | CPU_user | CPU_system | usage | percent | + percent + ------------ + ----------- + ------------ + percent + | starting | 0.000415 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000011 | 0.000000 | 0.000000 | 0 | checking permissions | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 | | checking permissions | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000056 | 0.001000 | 0.000000 | 0 | 0 | checking permissions | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | checking permissions | 0.000009 | 0.000000 | 0.000000 | 0 | 0 | Opening tables | 0.000225 | 0.000000 | 0.000000 | 0 | 0 | System lock | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | init | 0.000138 | 0.000000 | 0.000000 | 0 | optimizing | 0.000046 | 0.000000 | 0.000000 | 0 | 0 | statistics | 0.001115 | 0.001000 | 0.000000 | 0 | 0 | preparing | 0.001246 | 0.002000 | 0.000000 | 0 | 0 | FULLTEXT initialization | 0.000088 | 0.000000 | 0.000000 | 0 | 0 | Creating tmp table | 0.000057 | 0.000000 | 0.000000 | 0 | 0 | executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | Copying to tmp table | 120.430834 | 81.227651 | 38.749110 | 1112 | 0 | Sorting result | 0.000058 | 0.000000 | 0.000000 | 0 | 0 | Sending data | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | removing tmp table | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | end | 0.000041 | 0.001000 | 0.000000 | 0 | 0 | query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | closing tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | freeing items | 0.008546 | 0.000000 | 0.007999 | 0 | 0 | logging slow query | 0.000008 | 0.000000 | 0.000000 | 0 | logging slow query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | | 0 | + ------------------------- + ------------ + ----------- + ------------ + -------------- + ----------------- +

Copying to tmp table occupies a lot of cpu operations.

It seems that the mysql optimizer is too weak, and we need to use the index forcibly! Force index (primary), which forces the use of the primary key of the pd table