0

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.

2
  • I think you need OR instead of AND for the last line.
    – Rick James
    Commented Apr 29, 2015 at 22:24
  • Thanks for the response Rick but same result...
    – BK435
    Commented Apr 29, 2015 at 22:39

1 Answer 1

2

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.

2
  • 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
    Commented May 6, 2015 at 1:21
  • GROUP BY 1 is a shortcut for GROUP BY whatever-the-first-field-in-SELECT-is. In this case TABLE_SCHEMA. (etc)
    – Rick James
    Commented May 6, 2015 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.