I have been working a bit with MySQL and SQL Server, and I have a question: si el script "Missing Index" existe en MySQL
In SQL Server I know that it shows the missing indexes of each table in a database, and for that I would like to know if it exists in MySQL or something similar to this SQl Server script that can be executed.
I would thank you very much in advance.
If you want to show the tables in the database that do not have an index, you could do it with this query:
select
t.table_schema, t.table_name
from
information_schema.tables t
inner join information_schema.columns c
on t.table_schema=c.table_schema and t.table_name=c.table_name
where
t.table_schema='AQUÍ-NOMBRE-DE-TU-BASE-DE-DATOS'
group by
t.table_schema,t.table_name
having
sum(if(column_key in ('PRI','UNI'), 1,0)) =0
;
In the where t.table_schema= you must indicate the name of your database.
I've tried it and it works for me.
Note: This works faster than the comment.
If I work the two codes sent by @ A.Cedano and modify it a little bit like this:
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME,
IFNULL(kcu.CONSTRAINT_NAME, 'Not indexed') AS 'Index'
FROM information_schema.TABLES t
INNER JOIN information_schema.'COLUMNS' c
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.COLUMN_NAME LIKE '%Id'
LEFT JOIN information_schema.'KEY_COLUMN_USAGE' kcu
ON kcu.TABLE_SCHEMA = t.TABLE_SCHEMA
AND kcu.TABLE_NAME = t.TABLE_NAME
AND kcu.COLUMN_NAME = c.COLUMN_NAME
AND kcu.ORDINAL_POSITION = 1
WHERE kcu.TABLE_SCHEMA IS NULL
AND t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql');