Does MySQL Multi-Column Index Partial Match Work?

Asked 2 years ago, Updated 2 years ago, 80 views

CREATE TABLE `users`(
  `id`CHAR(36) NOT NULL,
  `tenant_id`CHAR(36) NOT NULL,
  `email_address`VARCHAR(254) NOT NULL,
  PRIMARY KEY(`id`),
  INDEX`email_idx`(`email_address`ASC))

In the case of the above table, if I search below, will the index be heard or effective?

SELECT* FROM users WHERE email_address=?AND tenant_id=?

Although it does not exactly match the index, I would like to make sure that email_address is the first one, and that email_idx index works, and that tenant_id matches the result.

mysql database

2022-09-29 22:51

1 Answers

It works.
If you try running the SELECT statement with EXPLAIN, you will find that email_idx is actually used.

MySQL 5.6 - Multi-Column Index
https://dev.mysql.com/doc/refman/5.6/ja/multiple-column-indexes.html


2022-09-29 22:51

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.