Search code examples
mysqldatabaseinformation-schema

Find multi-column index in information_schema

I have a table with an index comprised of three columns in a table.

Is there a way I can find this in information_schema, or will it only show me three separate indices?

Solution

  • Credits to Radek Postolowicz for steering me in the right direction:

    SELECT 
  table_name AS `Table`,
  index_name AS `Index`,
  GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics 
WHERE table_schema = 'db' 
AND table_name='tbl' 
GROUP BY 1,2;