Usage of MySQL indexes

Question

I have a very simple table with five columns,

CREATE TABLE notification_tag ( _id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, notification_id INT NOT NULL, tag_value CHAR(11) NOT NULL, recipient CHAR(11) NOT NULL, brand CHAR(11), INDEX tag_value (tag_value), INDEX notification_id_tag_value_recipient_brand (notification_id, tag_value, recipient, brand) ) CHARACTER SET ascii COLLATE ascii_bin;

Explain shows that MySQL is using the key tag_value when I run the below query,

select * from notification_tag where recipient='user' and tag_value='doc1' and (brand='brand' or brand is null);

+--+-----------+----------------+----------+-------------------------------------------------------------+----+---------+-------+-----+----+--------+-----------+ |id|select_type|table |partitions|possible_keys |type|key |key_len|ref |rows|filtered|Extra | +--+-----------+----------------+----------+-------------------------------------------------------------+----+---------+-------+-----+----+--------+-----------+ |1 |SIMPLE |notification_tag|NULL |tag_value,notification_id_tag_value_recipient_brand|ref |tag_value|11 |const|1 |100 |Using where| +--+-----------+----------------+----------+-------------------------------------------------------------+----+---------+-------+-----+----+--------+-----------+

Is there any reason for not to use notification_id_tag_value_recipient_brand index?

Bill Karwin · Accepted Answer · 2023-01-31 04:21:00Z

Your query will not use the index on (notification_id, tag_value, recipient, brand) because the query doesn't have any term to search the leftmost column of the index.

Think of a telephone book. It helps if you search people by last name, or by last name and first name. But if you search only by first name, the order of the entries in the book doesn't help.

You might also like my presentation How to Design Indexes, Really, or the video.

Collectives™ on Stack Overflow

Usage of MySQL indexes

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related