How do I find out which tables have no indexes in MySQL

Question

I am dealing with a database that has about 300 tables and I am looking for a way to find all tables that have NO indexes (excluding PRIMARY). Ideally I would like to get back a result set that gives me a count of the number of indexes per table. I have tried this using a query like this:

SELECT 
  table_name, column_name, index_name, count(table_name) as index_count 
FROM 
  information_schema.statistics 
WHERE 
  index_name != 'primary' 
AND
  table_schema = 'your_database'
GROUP BY
  table_name

but it does not return accurate results. any help would be appreciated

Answer 1

this will result those tables that have no indexes at all (not even primary key)

select * from INFORMATION_SCHEMA.tables
where table_schema = 'your_database'
and table_name not in 
(
select  table_name -- , count(*) 
from (
SELECT  table_name, index_name
FROM information_schema.statistics 
WHERE table_schema = 'your_database'
GROUP BY  table_name, index_name) tab_ind_cols
group by table_name
)

Answer 2

select 
    t.table_schema,t.table_name,engine 
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 
group by 
    t.table_schema,t.table_name   
having 
    sum(if(column_key in ('PRI','UNI'), 1,0)) = 0;

This select works but it returns if table is not PRIMARY or Unique