PHP: List the indexes on a MySQL table using PDO.

This is a tutorial on how to list all of the indexes that have been assigned to a MySQL table. To do this, we will be using PHP’s PDO class.

Take a look at the following code snippet:

//Connect to MySQL
$pdo = new PDO($dsn, $username, $password);

//SQL query that shows all indexes that belong
//to a certain MySQL table.
$sql = "SHOW INDEXES FROM members";
$stmt = $pdo->query($sql);

//Loop through the resultset and var_dump
//out the index details.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    var_dump($row);
}

In the PHP above, we:

  1. Connected to MySQL using the PDO object.
  2. Afterwards, we executed a “SHOW INDEXES” SQL query. In this particular case, we are selecting all indexes that belong to a table called members.
  3. Finally, we loop through the indexes and var_dump the rows.

If you test the code above, you will see that each $row contains information about every column in our table that has an index on it.

A drill-down of the columns that are returned:

  • Table: This is the name of the table that the index belongs to.
  • Non_unique: If this is set to 1, then it means that the index in question is NOT unique.
  • Key_name: This is the name of the index. In some cases, it will be the same name as the column it has been added to.
  • Seq_in_index: This is the position of the column in the index. For example: If this is set to 2, then it means that this column is 2nd in the index.
  • Column_name: This is the name of the column that the index has been added to.

You will also be given columns such as Cardinality, Sub_part and Index_comment, etc.