How to check if an index exists on a table field in MySQL

How do I check if an index exists on a table field in MySQL?

I've needed to Google this multiple times, so I'm sharing my Q/A.

Use SHOW INDEX like so:

SHOW INDEX FROM [tablename]

Docs: https://dev.mysql.com/doc/refman/5.0/en/show-index.html

Try:

SELECT * FROM information_schema.statistics 
  WHERE table_schema = [DATABASE NAME] 
    AND table_name = [TABLE NAME] AND column_name = [COLUMN NAME]

It will tell you if there is an index of any kind on a certain column without the need to know the name given to the index. It will also work in a stored procedure (as opposed to show index)

SHOW KEYS FROM  tablename WHERE Key_name='unique key name'

will show if a unique key exists in the table.

show index from table_name where Column_name='column_name';

Use the following statement:

SHOW INDEX FROM *your_table*

And then check the result for the fields: row["Table"], row["Key_name"]

Make sure you write "Key_name" correctly

To look at a table's layout from the CLI, you would use

desc mytable

or

show table mytable

Adding to what GK10 suggested:

> Use the following statement: SHOW INDEX FROM your_table > > And then check the result for the fields: row["Table"], > row["Key_name"] > > Make sure you write "Key_name" correctly

One can take that and work it into PHP (or other language) wrapped around an sql statement to find the index columns. Basically you can pull in the result of SHOW INDEX FROM 'mytable' into PHP and then use the column 'Column_name' to get the index column.

Make your database connection string and do something like this:

$mysqli = mysqli_connect("localhost", "my_user", "my_password", "world");

$sql =  "SHOW INDEX FROM 'mydatabase.mytable' WHERE Key_name = 'PRIMARY';" ;
$result = mysqli_query($mysqli, $sql);

while ($row = $result->fetch_assoc()) {
    echo $rowVerbatimsSet["Column_name"];
}

You can use the following SQL to check whether the given column on table was indexed or not:

select  a.table_schema, a.table_name, a.column_name, index_name
from    information_schema.columns a
join    information_schema.tables  b on a.table_schema  = b.table_schema and
                                    a.table_name = b.table_name and 
                                    b.table_type = 'BASE TABLE'
left join (
 select     concat(x.name, '/', y.name) full_path_schema, y.name index_name
 FROM   information_schema.INNODB_SYS_TABLES  as x
 JOIN   information_schema.INNODB_SYS_INDEXES as y on x.TABLE_ID = y.TABLE_ID
 WHERE  x.name = 'your_schema'
 and    y.name = 'your_column') d on concat(a.table_schema, '/', a.table_name, '/', a.column_name) = d.full_path_schema
where   a.table_schema = 'your_schema'
and     a.column_name  = 'your_column'
order by a.table_schema, a.table_name;

Since the joins are against INNODB_SYS_*, the match indexes only came from the INNODB tables only.

If you need to check if a index for a column exists as a database function, you can use/adopt this code. If you want to check if an index exists at all regardless of the position in a multi-column-index, then just delete the part AND SEQ_IN_INDEX = 1.

DELIMITER $$
CREATE FUNCTION `fct_check_if_index_for_column_exists_at_first_place`(
    `IN_SCHEMA` VARCHAR(255),
    `IN_TABLE` VARCHAR(255),
    `IN_COLUMN` VARCHAR(255)
)
RETURNS tinyint(4)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Check if index exists at first place in sequence for a given column in a given table in a given schema. Returns -1 if schema does not exist. Returns -2 if table does not exist. Returns -3 if column does not exist. If index exists in first place it returns 1, otherwise 0.'
BEGIN

-- Check if index exists at first place in sequence for a given column in a given table in a given schema. 
-- Returns -1 if schema does not exist. 
-- Returns -2 if table does not exist. 
-- Returns -3 if column does not exist. 
-- If the index exists in first place it returns 1, otherwise 0.
-- Example call: SELECT fct_check_if_index_for_column_exists_at_first_place('schema_name', 'table_name', 'index_name');

-- check if schema exists
SELECT 
    COUNT(*) INTO @COUNT_EXISTS
FROM 
    INFORMATION_SCHEMA.SCHEMATA
WHERE 
    SCHEMA_NAME = IN_SCHEMA
;

IF @COUNT_EXISTS = 0 THEN
    RETURN -1;
END IF;


-- check if table exists
SELECT 
    COUNT(*) INTO @COUNT_EXISTS
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE
;

IF @COUNT_EXISTS = 0 THEN
    RETURN -2;
END IF;


-- check if column exists
SELECT 
    COUNT(*) INTO @COUNT_EXISTS
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE
AND COLUMN_NAME = IN_COLUMN
;

IF @COUNT_EXISTS = 0 THEN
    RETURN -3;
END IF;

-- check if index exists at first place in sequence
SELECT 
    COUNT(*) INTO @COUNT_EXISTS
FROM 
    information_schema.statistics 
WHERE 
    TABLE_SCHEMA = IN_SCHEMA
AND TABLE_NAME = IN_TABLE AND COLUMN_NAME = IN_COLUMN
AND SEQ_IN_INDEX = 1;


IF @COUNT_EXISTS > 0 THEN
    RETURN 1;
ELSE
    RETURN 0;
END IF;


END$$
DELIMITER ;

Try to use this:

SELECT TRUE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = "{DB_NAME}" 
AND TABLE_NAME = "{DB_TABLE}"
AND COLUMN_NAME = "{DB_INDEXED_FIELD}";

You can't run a specific show index query because it will throw an error if an index does not exist. Therefore, you have to grab all indexes into an array and loop through them if you want to avoid any SQL errors.

Heres how I do it. I grab all of the indexes from the table (in this case, leads) and then, in a foreach loop, check if the column name (in this case, province) exists or not.

$this->name = 'province';

$stm = $this->db->prepare('show index from `leads`');
$stm->execute();
$res = $stm->fetchAll();
$index_exists = false;

foreach ($res as $r) {
	if ($r['Column_name'] == $this->name) {
		$index_exists = true;
	}
}

This way you can really narrow down the index attributes. Do a print_r of $res in order to see what you can work with.