what is wrong with this query?

[Tarantulus]

Hi,

I've written a query to clean up my database, but it seems to put a heavy load on the system and hang when I run it:

DELETE FROM CPU WHERE FROM_UNIXTIME( `datetime` , '%i' ) IN (SELECT FROM_UNIXTIME( `datetime` , '%i' ) AS `butt`  FROM `cpubak` HAVING MOD(`butt` , 5 ));

[Atli]

Hi.

What does EXPLAIN give you?

My guess is that the frequent calls to FROM_UNIXTIME are causing the stress.

I'm trying to understand the HAVING MOD thing there.
As I see it, the MOD will return an integer, which the HAVING clause would then interpret as the value to match against... what?
Or does it simply evaluate it as a boolean?

[Tarantulus]

EXPLAIN gives

+----+--------------------+--------+------+---------------+------+---------+------+-------+-------------+
| id | select_type        | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+--------------------+--------+------+---------------+------+---------+------+-------+-------------+
|  1 | PRIMARY            | CPU    | ALL  | NULL          | NULL | NULL    | NULL | 31401 | Using where | 
|  2 | DEPENDENT SUBQUERY | cpubak | ALL  | NULL          | NULL | NULL    | NULL | 40997 |             | 
+----+--------------------+--------+------+---------------+------+---------+------+-------+-------------+

I thought the same about MOD, but it works as a boolean

[Atli]

Ok, it uses no indexes :S

I'm not sure if that is because of the functions or because there simply are no indexes.
Do you have indexes on the `datetime` fields of your tables?

If not, try adding them:
[code=mysql]
ALTER TABLE `CPU` ADD INDEX `CPU_datetime`( `datetime`);
ALTER TABLE `cpubak` ADD INDEX `cpuback_dateti me`(`datetime`) ;[/code]

Also, try replacing HAVING in your sub-query with WHERE.
Not sure exactly what you are trying to achieve with this command, but as I understand it, in this situation HAVING and WHERE should work the same.

And P.S.
Don't test this on data you can't lose... because you might :P

[Tarantulus]

Thanks for the input.

I've decided this all isn't worth the effort of saving a few 10's of MB on the database after all.

Cheers for trying anyway