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

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开发者_如何学Go 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

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
)

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

this will return the number of indexes for all tables

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

An useful tool for situations like this is also to enable MySQL slow query log. You need to enable it on my.cnf, but because only queries taking longer than set amount of time are logged, you can leave it turned on even on production systems.

With that in place, queries taking long time to answer are logged and you can then see what made them slow.

bpgergo, is on to something here.

And I believe this is what you are looking for

-- ============================================================================
-- TABLES WITHOUT INDICES BUT HAVE A PRIMARY KEY
-- ============================================================================
SELECT *
FROM INFORMATION_SCHEMA.TABLES AS MAIN_TABLE
WHERE
    TABLE_SCHEMA = 'your_database'
    -- ============================================================================
    -- FIND TABLES WITH A PRIMARY KEY
    -- ============================================================================
    AND TABLE_NAME IN (
                SELECT TABLE_NAME
                FROM (
                        SELECT TABLE_NAME, INDEX_NAME, COUNT(INDEX_NAME) AS TEST
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = 'your_database'
                AND INDEX_NAME = 'PRIMARY'
        GROUP BY TABLE_NAME , INDEX_NAME) AS TAB_IND_COLS
    GROUP BY TABLE_NAME)
    -- ============================================================================
    -- FIND TABLES WITH OUT ANY INDICES
    -- ============================================================================
AND TABLE_NAME NOT IN (
        SELECT TABLE_NAME
        FROM (
                SELECT TABLE_NAME, INDEX_NAME, COUNT(INDEX_NAME) AS TEST
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = 'your_database'
                AND INDEX_NAME <> 'PRIMARY'
        GROUP BY TABLE_NAME , INDEX_NAME) AS TAB_IND_COLS
    GROUP BY TABLE_NAME
    )
;

You will ONLY need to change your_database to get this to work.