The usage of MySQL single-column index and federated index

This article focuses on "the use of MySQL single-column index and federated index". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn the use of MySQL single-column index and federated index.

This paper introduces the selection of single-column index and federated index by optimizer through a case.

There are two indexes on the ord_seq field of the order table, the single column index (order_seq) and the federated index (order_seq,order_type)

MySQL > explain select * from `order` where order_seq = 15021312577 +- -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | order | NULL | ref | idx_ordseq Idx_ordseq_ordtype | idx_ordseq | 8 | const | 1 | 100.00 | NULL | +-+-- -+

You can see that the optimizer chose the single-column index idx_ordseq (order_seq) instead of the federated index. Because the leaf node of the index contains a single key value, theoretically a page can hold more records.

If you change the scene:

MySQL > explain select * from `order` where order_seq = 1502131212577 order by order_type desc +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | order | NULL | ref | idx_ordseq Idx_ordseq_ordtype | idx_ordseq_ordtype | 8 | const | 1 | 100.00 | Using where | + -+-+ 1 row in set 1 warning (0.00 sec)

The joint index of where condition field and sort field is added to solve the problem of filesort.

At this point, I believe you have a deeper understanding of "the use of MySQL single-column index and federated index". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!