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 TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL Show index
2022-03-10 21:56:14

MySQL Show index

This query helps to access and retrieve table information. This syntax displays the required index or all indexes of the table. It also displays the index type as per the storage engine.

Syntax

The basic syntax of the show index is given below.

SHOW INDEX from table name;

The show index syntax with database name is shown below.

SHOW INDEX FROM table name IN database name;

The show index syntax with database name is shown below.

SHOW INDEX IN table name FROM database name;

The show index syntax with database name is shown below.

SHOW INDEX FROM database.table;

The show index syntax with the WHERE clause is shown below.

SHOW INDEX from table name WHERE condition;

The show index syntax using the keys clause is shown below.

SHOW KEYS from table name IN database name;

Examples of the show index

1) Example: The basic show index example is shown below:

Execute the below query to see table indexes and their information.

mysql> SHOW indexes FROM index_table;

OUTPUT

MySQL Show Index

The above output image shows indexes from the table. The image shows the table name, unique key, key name, column name, index type, and table visibility.

2) Example: The basic show index with database example is shown below:

Execute the show indexes statement, including database and table name. You will use the required table name from a specific table name.The "show indexes" work with IN and FROM clauses.

mysql> SHOW indexes FROM index_table IN tutorial;

OUTPUT

MySQL Show Index

3) Example: The basic show index example with database name is shown below.

Execute the show indexes statement using database and table name.

mysql> SHOW indexes IN index_table FROM tutorial;

OUTPUT

MySQL Show Index

The above image shows indexes from the table. The image shows the table name, unique key, index name, column name, index type, collation, and table visibility.

4) Example: The basic show index example with database name is shown below.

Execute the show indexes statement with database and table name. Here we will use the required table name. The "show indexes" works with FROM clause and dot (.) symbol.

mysql> SHOW indexes FROM tutorial. index_table;

OUTPUT

MySQL Show Index

The above output image shows indexes from the table. The image shows the table name, unique key, sequence of the index, index name, column name, index type, and table visibility.

5) Example: The basic show index example with the WHERE clause is shown below.

Execute show indexes statement with WHERE clause. Here, you will use conditions on the index column. The condition applies to the visible column. The visible column assigns the "YES" condition.

mysql> SHOW indexes IN index_table FROM tutorial where visible = 'YES';

OUTPUT

MySQL Show Index

The above image shows indexes from the table. The output image shows the table name, sequence of the index, index name, column name, index type, and table visibility. The visible apply to the "YES" condition.

6) Example: The basic show index with key example is shown below.

Execute the following query to get the key name of the table. Here we will use the "keys" keyword instead of the index keyword.

mysql> SHOW keys FROM index_table IN tutorial;

OUTPUT

MySQL Show Index

The above output image shows indexes from the table. The image shows the table name, unique key, sequence of the index, key name, column name, index type, and table visibility. The key name is known as an index column.