Query databases that have MYISAM tables that do not have only fulltext index tables

Question

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.

• Rick James: I think you need OR instead of AND for the last line.
• BK435: Thanks for the response Rick but same result...

Answer 1

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.

• Rick James: 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 HAVING FT_indexes = 0
• Rick James: GROUP BY 1 is a shortcut for GROUP BY whatever-the-first-field-in-SELECT-is. In this case TABLE_SCHEMA. (etc)