English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

When should you use a composite index in MySQL?

When using subqueries, we can use composite indexes. The advantages of using composite indexes are that they can be.

  • Join

  • Filtering

  • Selecting in

The syntax of index is as follows.

index(column_name1column_name2column_name3column_name4............................N)

Let's first create a table and set an index in it.

mysql> create table MultipleIndexDemo
   - > (
   - > id int,
   - > FirstName varchar(100),
   - > LastName varchar(100),
   - > Address varchar(200),
   - > index(id,LastName,Address)
   - > ;

Check if the index is successfully created.

mysql> show index from MultipleIndexDemo;

The following is the output.

+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| multipleindexdemo |          1 | id |            1 | id | A | AAAAAAAA | 0 | NULL | NULL | YES | BTREE | AAAAAAAA | AAAAAAAA | AAAAAAAA | YES |
| multipleindexdemo |          1 | id |            2 | LastName | A | AAAAAAAA | 0 | NULL | NULL | YES | BTREE | AAAAAAAA | AAAAAAAA | AAAAAAAA | YES |
| multipleindexdemo |          1 | id |            3 | Address | A | AAAAAAAA | 0 | NULL | NULL | YES | BTREE | AAAAAAAA | AAAAAAAA | AAAAAAAA | YES |
+-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.18 sec)