MySQL – Query Databases with MYISAM Tables Lacking Fulltext Index

indexinformation-schemamyisamMySQL

I am trying to query from the information_schema where the database (table_schema) has MYISAM tables but does not have only fulltext index tables.

The statistics tables is very poorly designed for the query that I need. If you look, the table_schema and table_name appear multiple times. Thus querying where table_name != 'fulltext' is not an option because there could be another row in statistics table for that table that is Btree which usually the case for PK indexes on tables. This results in that table that has a fulltext coming back using the above mentioned where clause in query.

So with that being said here is my Query so far:

SELECT DISTINCT
    (a.TABLE_SCHEMA)
FROM
    information_schema. TABLES a
INNER JOIN information_schema.statistics b ON a.TABLE_NAME = b.TABLE_NAME,
 (
    SELECT DISTINCT
        c.TABLE_SCHEMA,c.table_name
    FROM
        information_schema.statistics c
    WHERE
        c.index_type = 'FULLTEXT'

) AS fullTextTables
WHERE
    a. ENGINE = 'MyISAM'
AND a.table_schema NOT IN (
    'information_schema',
    'mysql',
    'performance_schema'
)
AND (a.Table_schema != fullTextTables.Table_schema AND b.table_name != fullTextTables.table_name);

The problem is that when I run this I am still getting back databases that only have 1 or 2 tables left in them that have not been converted from MYISAM; those tables still have Fulltext indexes. The Mysql version is 5.5 and not 5.6. Note: upgrading at this point is not an option.

Any help amending this query to get proper results would be appreciated.

Best Answer

MyISAM tables with FULLTEXT index and other index(es).

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.statistics AS s1
    JOIN  information_schema.statistics AS s2  USING (TABLE_SCHEMA, TABLE_NAME)
    JOIN  information_schema.TABLES AS t       USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE  s1.index_type = 'FULLTEXT'
      AND  s2.index_type != 'FULLTEXT'
      AND  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA  NOT IN ('information_schema', 'mysql', 'performance_schema');

# MyISAM tables with no FT index: 

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.TABLES AS t
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
      AND  NOT EXISTS 
      ( SELECT  *
            FROM  information_schema.statistics
            WHERE  index_type = 'FULLTEXT'
              AND  TABLE_SCHEMA = t.TABLE_SCHEMA
              AND  TABLE_NAME = t.TABLE_NAME 
      );

# MyISAM tables, counting index_types:

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME,
        SUM(index_type = 'FULLTEXT') AS FT_indexes,
        SUM(index_type != 'FULLTEXT') AS others
    FROM  information_schema.TABLES AS t
    JOIN  information_schema.statistics USING(TABLE_SCHEMA, TABLE_NAME)
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
    GROUP BY  1,2;

Tack on a HAVING FT_indexes = 0 to get just those with no FT index.

Etc.

Related Question