1

There is a column created_at in most of tables.

I would like to get the details if that field exists in the table and it is not indexed.

Is there any thing can be done with the information_schema to get this checked?

I'm using MySQL 5.6

0

To check the column properties you can run the query

SELECT * FROM information_schema.columns WHERE table_schema = 'your_schema_name' 
AND COLUMN_NAME = 'created_at';

This will list all the tables in your schema with the columns created_at. Here you can check some of the column properties including the COLUMN_KEY with values PRI, UNI, MUL.

For a detailed view of the table index run the below query against the tables returned by the first query. SHOW CREATE TABLE table_name;

0

You need to examine one of the following:

EXAMPLE #1

  • You have a table called mydb.mytable
  • You have a columns called mycolumn
  • You want to know if mycolumn is indexed at all

Query #1

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE table_schema='mydb'
AND table_name='mytable'
AND column_name='mycolumn';

Query #2

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema='mydb'
AND table_name='mytable'
AND column_name='mycolumn';

EXAMPLE #2

  • You want to know if mycolumn is indexed at all
  • You do not know which table has an index on that column

Query #1

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE column_name='mycolumn';

Query #2

SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE column_name='mycolumn';

EXAMPLE #3

You want to know which table has a column called mycolumn that is not indexed anywhere

SELECT A.table_schema,A.table_name FROM
(
    SELECT table_schema,table_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE column_name='mycolumn'
) A
LEFT JOIN
(
    SELECT DISTINCT table_schema,table_name
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE column_name='mycolumn'
) B USING (table_schema,table_name)
WHERE B.table_schema IS NULL;
  • I tried: create table testing ( created_at char(8) not null ); create index x1 on testing ( created_at ); but there is no information in information_schema.key_column_usage. This was for 10.1.26-MariaDB so there might be a difference from MySQL. – Lennart Oct 12 '17 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.