Mastering MySQL Indexes: Costs, Pitfalls, and Best Practices

Introduction

The article explains how to use MySQL indexes effectively. An InnoDB table user_innodb with more than 5 million rows is created, containing columns id (primary key), name, gender, phone, and a composite index on (name, phone):

CREATE TABLE `user_innodb` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX IDX_NAME_PHONE (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;