CODEWITHSUNDEEP
mysqlstored-proceduresindexing
Olizt
Olizt

Reputation: 161

Check if a column is indexed - MySql

I need to write a script to check if a column is indexed in the table it belongs to, if not then add an index for it. MySQL doesn't seem to have ADD INDEX IF NOT EXISTS.

What's the best way to do this in MySQL?

Upvotes: 3

Views: 2543

Answers (2)

Eljakim
Eljakim

Reputation: 6937

See http://dev.mysql.com/doc/refman/5.1/en/create-index.html

There is a stored procedure that does this (remember, not my code, just the first hit on google with mysql add index if not exists as search string).

DELIMITER $$
DROP PROCEDURE IF EXISTS `create_index_if_not_exists`$$

CREATE DEFINER=`user`@`%` PROCEDURE `create_index_if_not_exists`(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(200))
SQL SECURITY INVOKER
BEGIN

set @Index_cnt = (
select  count(1) cnt
FROM    INFORMATION_SCHEMA.STATISTICS
WHERE   table_name = table_name_vc
and index_name = index_name_vc
);

IF ifnull(@Index_cnt,0) = 0 THEN set @index_sql = concat('Alter table ',table_name_vc,' ADD INDEX ',index_name_vc,'(',field_list_vc,');');

PREPARE stmt FROM @index_sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END IF;

END$$
DELIMITER ;

There is an alternative version right underneath it in the same page.

Upvotes: 4

abcde123483
abcde123483

Reputation: 3905

Maybe this can help you along:

SHOW INDEX FROM my_table WHERE Key_name = 'index_to_check';

Upvotes: 1

Related Questions