Linux 4 All

Best Linux tricks source

Posts in the InnoDB category

Here is how:

SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics WHERE index_type LIKE ‘FULLTEXT%’;

This is important as MySQL prior to 5.6 does not support FULLTEXT InnoDB tables.

I admit that is pretty lame, but until today I had no idea how to kill a specific MySQL query. It is quite easy.

First we need to get the queries:

mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id   | User | Host      | db   | Command | Time | State | Info             |
+------+------+-----------+------+---------+------+-------+------------------+
| 5585 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

Then kill the id:

mysql> kill 5585;
ERROR 1317 (70100): Query execution was interrupted
mysql>

Get MySQL InnoDB tables

Run this query to get all InnoDB tables:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

Change engine='innodb' to engine='myisam' to get MyISAM tables.