"Cannot convert to a SELECT statement" What does this mean

Question

I'm trying to make our mySQL database run faster and I've analyzed our slow query log and the most common slow query is this:

CREATE TABLE IF NOT EXISTS `wp_bad_behavior` (
 `id` INT(11) NOT NULL auto_increment,
 `ip` TEXT NOT NULL,
 `date` DATETIME NOT NULL default '0000-00-00 00:00:00',
 `request_method` TEXT NOT NULL,
 `request_uri` TEXT NOT NULL,
 `server_protocol` TEXT NOT NULL,
 `http_headers` TEXT NOT NULL,
 `user_agent` TEXT NOT NULL,
 `request_entity` TEXT NOT NULL,
 `key` TEXT NOT NULL,
 INDEX (`ip`(15)),
 INDEX (`user_agent`(10)),
 PRIMARY KEY (`id`) );

I'm trying to understand why this query keeps getting called because after the table is setup it should not keep happening.

The EXPLAIN result for this is: Cannot convert to a SELECT statement.

Any ideas on this would be fantastic!

Paul

Answer 1

Of course you can't convert a CREATE statement to a SELECT statement...

The question therefore is, why a creation statement is called so frequently. It uses mysql's IF NOT EXISTS, so it might not even be a design issue, the table would only get created once despite the query is called bazillion times. Maybe your system that uses the database issues this statement from every single method, as a way to make sure actual database structure matches its expectations. Just a foolprof in case somebody deletes this super important table.

Answer 2

Its probably there as an easy way to recover from someone deleting the table. A good reason why someone would delete the table is that it's an easy way to get rid of old log files. However this obviously affects performance and so you should check that none of the code is deleting the table and then remove this check. Then you will be required to manually recreate the table when deleting old logs.

Answer 3

EXPLAIN will only work on SELECT queries, that's why it complains. As to why the query is in the slow query log: Either it is deleted and recreated - check the regular query log for DROP TABLEs - or it simply blocks because the table/database is busy - check the other slow queries first, especially the ones on the same table.