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
Making slow queries fast using composite indexes in MySQL
Let us first see what is Composite Index −
A composite index is an index that is used on multiple columns.
It is also known as a multiple-column index.
MySQL allows the user to create a composite index which can consist of up to 16 columns.
The query optimizer uses the composite indexes for queries which will test all columns in the index.
It can also be used for queries which will test the first columns, the first two columns, and so on.
If the columns are specified in the right order in the index definition, a single composite index can be used that would speed up certain kinds of queries on the same table.
Let us see how a composite index can be created, during the creation of a table. It can be done using the below statement −
Query
CREATE TABLE table_name ( c1 data_type PRIMARY KEY, c2 data_type, c3 data_type, c4 data_type, INDEX index_name (c2,c3,c4) );
In the above statement, the composite index consists of three columns c2, c3, and c4.
A composite index can also be added into an existing table using the ‘CREATE INDEX’ statement. Let us see how this can be done −
Query
CREATE INDEX index_name ON table_name(c2,c3,c4);
Let us see how sloq queries can be made fast using Composite Index −
The speed of the query execution depends on its duration.
Using index hints will enace the query speed.
The MySQL optimizer can be used to make the right decision while choosing an index.
But this should be done only on static queries.
If the queries are added where the ‘WHERE’ clause change, the performance of the queries will worsen, since it won’t let the optimizer do its job.
The ‘FORCE INDEX’ statement acts like ‘USE INDEX (index_list), in addition that a table scan is considered as an expensive task.
A table scan is required only if there is no way to use a named index to find the rows in a table.
430 Views
