Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
When should I use a composite index in MySQL?
The composite index can be used when we are using sub queries. The advantages of using composite index are in case of.
- Joining
- Filtering
- Selecting
The following is the syntax of index.
index(column_name1,column_name2,column_name3,column_name4,............................N)
Let us create a table first and within that we have set index.
mysql> create table MultipleIndexDemo - > ( - > id int, - > FirstName varchar(100), - > LastName varchar(100), - > Address varchar(200), - > index(id,LastName,Address) - > ); Query OK, 0 rows affected (0.57 sec)
To check if the indexes are created successfully.
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 | 0 | NULL | NULL | YES | BTREE | | | YES | | multipleindexdemo | 1 | id | 2 | LastName | A | 0 | NULL | NULL | YES | BTREE | | | YES | | multipleindexdemo | 1 | id | 3 | Address | A | 0 | NULL | NULL | YES | BTREE | | | YES | +-------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ 3 rows in set (0.18 sec)
Updated on: 30-Jul-2019
439 Views
Advertisements