MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL Composite Index

Composite Index

A composite index is an index that is used on multiple columns. MySQL management system manages multiple columns simultaneously. Therefore, the single index contains multiple columns in one query called a composite index. It improves the speed of query performance during data retrieval.

This index contains a maximum of sixteen columns in one index. MySQL query optimizer uses this index to test the index column. This index is also known as a multiple-column index. This index assigns the column in the correct order.

Syntax

The basic syntax of the composite index shows below.

INDEX key name (column1, column2, column16)

The syntax of the MySQL composite index shows below.

 CREATE TABLE tname (
 Column1 data type NOT NULL PRIMARY KEY,
 Column2 datatype NOT NULL,
 Column3 datatype NOT NULL,
 INDEX iname (Column1, Column2, Column3)); 

The syntax of the MySQL composite index shows below.

 CREATE TABLE name (
 Column1 data type NOT NULL PRIMARY KEY,
 Column2 data type NOT NULL,
 Column3 data type NOT NULL,
 Column16 data type NOT NULL,
 INDEX iname (Column1, Column2, Column3, ...Column16)); 

The syntax of the MySQL composite index with the existing table shows below.

CREATE INDEXindex_nameON table name (Column1, Column2, Column3, ...Column16));

Examples of the Composite index

1) Example: The basic Composite indexexample shows below.

Execute the below query to understand the composite index in the MySQL table. Here, we have used two columns in a single index name.This index places the column in an ordered way in the table.

 mysql> create table index_table(
     ->roll_number INT NOT NULL PRIMARY KEY,
     -> phone INT NOT NULL ,
     ->name VARCHAR(10) NOT NULL,
     ->mark text NOT NULL,
     ->img blob NOT NULL,
     -> INDEX composites (roll_number, phone)); 

OUTPUT

Execute the below query to get the composite index and its information.

mysql> show index from index_table;
MySQL: Composite Index

The above output image shows a composite index of the column. The table shows the column name and sequence of the indexes. In addition, the output image also displays other index information in the table.

2) Example: The multiple Composite indexexample shows below.

Here, we have used four columns in a single index name. This index places an ordered column in the table.

 mysql> create table index_table(
     ->roll_number INT NOT NULL PRIMARY KEY,
     -> phone INT NOT NULL ,
     ->name VARCHAR(10) NOT NULL,
     ->mark text NOT NULL,
     ->img blob NOT NULL,
     -> INDEX composites (roll_number, phone, name, mark(10), img(200))); 

OUTPUT

Execute the below query to get the composite index and its information.

mysql> show index from index_table;
MySQL: Composite Index

The above output image shows the given composite index. The table image shows the key name and sequence of the indexes. In addition, the output image displays the column name and other index information in the table.

3) Example: The unordered Composite indexexample shows below.

Here, we have used two columns in a single index name. This index places an unordered column in the table.

 mysql> create table index_table(
     ->roll_number INT NOT NULL PRIMARY KEY,
     -> phone INT NOT NULL ,
     ->name VARCHAR(10) NOT NULL,
     ->mark text NOT NULL,
     ->img blob NOT NULL,
     -> INDEX composites (phone, mark(10), img(200),name)); 

OUTPUT

Execute the below query to get the composite index and its information.

mysql> show index from index_table;
MySQL: Composite Index

The table image shows the index name and sequence of the indexes. The above output image shows the given composite index for different columns. The image displays column names and other index information in the table. The sequence number shows as per order requirements.

4) Example: The composite index with the existing table example shows below.

Here, we have used three columns in a single index name. This index places an unordered column in the existing table.

mysql> CREATE INDEX composites ON index_table (phone, name, roll_number);

OUTPUT

Execute the below query to get the composite index and its information.

mysql> show index from index_table;
MySQL: Composite Index

The above output image shows the index name, column name, and sequence of the indexes. The output image shows the given composite index and its columns. The image displays column names and other index information in the table. The sequence number shows as per order requirements.