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

Question

I ve needed to Google this a couple times  so I m sharing my Q A

User · Answer

SHOW KEYS FROM  tablename WHERE Key name  unique key name    you can find if there exists an unique key in the table

User · Answer

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- gt name    province     stm    this- gt db- gt prepare  show index from  leads      stm- gt execute     res    stm- gt fetchAll     index exists   false   foreach   res as  r        if   r  Column name       this- gt 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

User · Answer

If you need the functionality if a index for a column exists  here at first place in sequence  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  gt  0 THEN     RETURN 1  ELSE     RETURN 0  END IF    END   DELIMITER

User · Answer

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

User · Answer

Try 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

User · Answer

to just look at a tables layout from the cli  you would do      desc mytable   or     show table mytable

User · Answer

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

User · Answer

Use SHOW INDEX like so   SHOW INDEX FROM  tablename    Docs  https   dev mysql com doc refman 5 0 en show-index html

User · Answer

show index from table name where Column name  column name

User · Answer

you can use the following SQL statement to check 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    so the match indexes only came  from INNODB tables only

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

Examples related to mysql

Examples related to indexing