Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Show Indexes

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Show Indexes

      A MySQL Index is a type of special lookup table that is used to make data retrieval easier in a database. It points to the actual data in the database.
      MySQL allows various types of indexes to be created on one or more columns in a table. They are:
      • Primary Key Index
      • Unique Index
      • Simple Index
      • Composite Index
      • Implicit Index
      To check if any of these indexes are defined on a table or not, MySQL provides the SHOW INDEX statement.

      The MySQL SHOW INDEX Statement

      The SHOW INDEX Statement of MySQL is used to list out the information about table index.
      The vertical-format output (specified by \G) in MySQL often is used with this statement, to avoid a long line wraparound.

      Syntax

      Following is the basic syntax of the SHOW INDEX Statement 
      SHOW INDEX FROM table_name;

      Example

      In this example, we are create a new table CUSTOMERS and adding a PRIMARY KEY index to one of its columns using the following CREATE TABLE query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),       
         PRIMARY KEY(ID),
         INDEX(NAME)
      );
      Now, we can display the indexes present on the CUSTOMERS table using the following SHOW INDEX query 
      SHOW INDEX FROM CUSTOMERS\G

      Output

      The vertical-output will be displayed as 
      *************************** 1. row ************************
              Table: customers
         Non_unique: 0
           Key_name: PRIMARY
       Seq_in_index: 1
        Column_name: ID
          Collation: A
        Cardinality: 0
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      *************************** 2. row ************************
              Table: customers
         Non_unique: 1
           Key_name: NAME
       Seq_in_index: 1
        Column_name: NAME
          Collation: A
        Cardinality: 0
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      2 rows in set (0.01 sec)

      With IN Clause

      In this example, let us first create an index on the AGE column of CUSTOMERS table using the following CREATE INDEX query 
      CREATE INDEX AGE_INDEX ON CUSTOMERS (AGE);
      You can also retrieve the information by specifying the database name as 
      SHOW INDEX IN CUSTOMERS FROM sample\G

      Output

      The output will be the same as above 
      *************************** 1. row ***************************
              Table: customers
         Non_unique: 0
           Key_name: PRIMARY
       Seq_in_index: 1
        Column_name: ID
          Collation: A
        Cardinality: 0
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      *************************** 2. row ***************************
              Table: customers
         Non_unique: 1
           Key_name: NAME
       Seq_in_index: 1
        Column_name: NAME
          Collation: A
        Cardinality: 0
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      2 rows in set (0.01 sec)

      With WHERE Clause

      As the indexes are displayed in a table format, we can use a WHERE clause with SHOW INDEX statement to retrieve specified indexes matching a given condition.
      SHOW INDEX IN CUSTOMERS WHERE Column_name = 'NAME'\G

      Output

      The index created on NAME column is displayed 
      *************************** 1. row ************************
              Table: customers
         Non_unique: 1
           Key_name: NAME
       Seq_in_index: 1
        Column_name: NAME
          Collation: A
        Cardinality: 0
           Sub_part: NULL
             Packed: NULL
               Null:
         Index_type: BTREE
            Comment:
      Index_comment:
            Visible: YES
         Expression: NULL
      1 row in set (0.00 sec)

      Show Indexes Using Client Program

      We can also display index information on a MySQL table using a client program.

      Syntax

      Following are the syntaxes to show indexes on a MySQL table using various programming languages
      PHPNodeJSJavaPython
      To show an index from MySQL table through a PHP program, we need to execute the SHOW INDEX statement using the query() function provided by mysqli connector as follows 
      $sql = "SHOW INDEX FROM tutorials_table";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      // SHOW INDEX
      $sql = "SHOW INDEX FROM tutorials_table";
      if ($index = $mysqli->query($sql)) {
          printf("Index shown successfully!.");
          while ($indx = mysqli_fetch_row($index)) {
              print_r($indx);
          }
      }
      if ($mysqli->errno) {
          printf("Index could not be shown!.", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Index shown successfully!.
      Array
      (
         [0] => tutorials_tbl
         [1] => 0
         [2] => PRIMARY
         [3] => 1
         [4] => tutorial_id
         [5] => A
         [6] => 3
         [7] =>
         [8] =>
         [9] =>
         [10] => BTREE
         [11] =>
         [12] =>
         [13] => YES
         [14] =>
      )
      Array
      (
         [0] => tutorials_tbl
         [1] => 0
         [2] => UIID
         [3] => 1
         [4] => tutorial_id
         [5] => A
         [6] => 3
         [7] =>
         [8] =>
         [9] =>
         [10] => BTREE
         [11] =>
         [12] =>
         [13] => YES
         [14] =>
      )