Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to optimize the joint index in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Explain select Fid from t_cyou_view_records where Fanchormasterid = 20237 and Froleid = 15081 and Fouttime is null order by Fentertime desc limit 1

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| | 1 | SIMPLE | t_cyou_view_records | ALL | NULL | NULL | NULL | NULL | 1709800 | Using where; Using filesort |

Looking at the cardinality of the two fields in the where condition, the establishment of a joint index will increase exponentially.

Show index from t_cyou_view_records

->

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | C |

| | t_cyou_view_records | 0 | PRIMARY | 1 | Fid | A | 1710010 | NULL | NULL | | BTREE |

| | t_cyou_view_records | 1 | idx_tcvr | 1 | Fanchormasterid | A | 132 | NULL | NULL | YES | BTREE |

| | t_cyou_view_records | 1 | idx_tcvrf | 1 | Froleid | A | 58965 | NULL | NULL | YES | BTREE |

Alter table t_cyou_view_records add index idx_tcvrf (Froleid,Fanchormasterid)

Query OK, 0 rows affected (7.79 sec)

Records: 0 Duplicates: 0 Warnings: 0

Explain select Fid from t_cyou_view_records where Fanchormasterid = 20237 and Froleid = 15081 and Fouttime is null order by Fentertime desc limit 1

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| | 1 | SIMPLE | t_cyou_view_records | ref | idx_tcvrf | idx_tcvrf | 18 | const,const | 3 | Using where; Using filesort |

The number of scan lines is reduced to 3

After reading the above, do you have any further understanding of how to optimize federated indexes in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.