Sleep within a SQL file chain?

[Kryptix]

I have a SQL file of about 80 queries that takes about 5 minutes to run, but my game server sometimes crashes due to SQL not responding whilst it's cycling through this.

 

Is there anyway to sleep within each query to slow things down and allow the game server to have some queries?

 

 

[fenway]

Yes, but that's a hack -- why are you queries so slow?

[Kryptix]

Yes, but that's a hack -- why are you queries so slow?

Doing queries like:

 

DELETE FROM `RSCEmulation`.`rscd_bank` WHERE `owner` NOT IN (SELECT `id` FROM `RSCEmulation`.`users`);

 

rscd_bank has 1,000,000 entries and users have 60,000 entries.

 

Not sure how I can speed them up really. They're all indexed properly AFAIK.

 

How can I sleep between each query to let the game have some time?

[fenway]

Did you try a LEFT JOIN?? NOT IN() is usually very, very slow.

[Kryptix]

Did you try a LEFT JOIN?? NOT IN() is usually very, very slow.

Could you give me an example please?

[fenway]

DELETE FROM `RSCEmulation`.`rscd_bank` LEFT JOIN `RSCEmulation`.`users` ON ( `RSCEmulation`.`rscd_bank`.`owner` = `RSCEmulation`.`users`.`id` ) WHERE `RSCEmulation`.`users`.`id` IS NULL;

[Kryptix]

DELETE FROM `RSCEmulation`.`rscd_bank` LEFT JOIN `RSCEmulation`.`users` ON ( `RSCEmulation`.`rscd_bank`.`owner` = `RSCEmulation`.`users`.`id` ) WHERE `RSCEmulation`.`users`.`id` IS NULL;

That runs at 1.87 and the NOT IN ran at 2.51 so yeah it's quicker. Thanks for that.

 

Could any of the other queries be improved?

 

DELETE FROM `RSCEmulation`.`rscd_players` WHERE UNIX_TIMESTAMP() > `delete_date` AND `delete_date` != '0';

 

UPDATE `RSCEmulation`.`users` SET `num_posts` = (SELECT COUNT(`id`) FROM `RSCEmulation`.`posts` WHERE `poster_id` = `users`.`id`);

 

UPDATE `RSCEmulation`.`rscd_experience` SET `exp_hits` = (((`exp_strength` + `exp_attack` + `exp_defense`) / 3) + 1154);

 

INSERT INTO `RSCEmulation Logs`.`web_statistics` (`time`, `total_accounts`, `accounts_today`, `total_characters`, `characters_today`, `online`, `online_unique`, `online_today`, `online_today_unique`, `total_topics`,  `topics_today`, `total_posts`, `posts_today`) VALUES (UNIX_TIMESTAMP(), (SELECT COUNT(`id`) FROM `RSCEmulation`.`users`), (SELECT COUNT(`id`) FROM `RSCEmulation`.`users` WHERE FROM_UNIXTIME(`registered`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()), (SELECT COUNT(`id`) FROM `RSCEmulation`.`rscd_players`), (SELECT COUNT(`id`) FROM `RSCEmulation`.`rscd_players` WHERE FROM_UNIXTIME(`creation_date`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()), (SELECT COUNT(`id`) FROM `RSCEmulation`.`rscd_players` WHERE `online` = '1'), (SELECT COUNT(DISTINCT(`login_ip`)) FROM `RSCEmulation`.`rscd_players` WHERE `online` = '1'), (SELECT COUNT(`id`) FROM `RSCEmulation`.`rscd_players` WHERE FROM_UNIXTIME(`login_date`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()), (SELECT COUNT(DISTINCT(`login_ip`)) FROM `RSCEmulation`.`rscd_players` WHERE FROM_UNIXTIME(`login_date`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()), (SELECT COUNT(`id`) FROM `RSCEmulation`.`topics`), (SELECT COUNT(`id`) FROM `RSCEmulation`.`topics` WHERE FROM_UNIXTIME(`posted`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()), (SELECT COUNT(`id`) FROM `RSCEmulation`.`posts`),(SELECT COUNT(`id`) FROM `RSCEmulation`.`posts` WHERE FROM_UNIXTIME(`posted`) BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY) AND NOW()));

 

DELETE FROM `RSCEmulation Logs`.`game_chat` WHERE `time` < UNIX_TIMESTAMP() - 604800;

 

UPDATE `RSCEmulation`.`rscd_experience` SET `total_xp` = (exp_attack + exp_defense + exp_strength + exp_hits + exp_ranged + exp_prayer + exp_magic + exp_cooking + exp_woodcut + exp_fletching + exp_fishing + exp_firemaking + exp_crafting + exp_smithing + exp_mining + exp_herblaw + exp_agility + exp_thieving + exp_runecrafting);

 

SET @num = 0;
UPDATE `RSCEmulation`.`rscd_experience` SET `attack_rank` = @num := @num +1 WHERE `exp_attack` > 0 ORDER BY `exp_attack` DESC;

[Kryptix]

DELETE FROM `RSCEmulation`.`rscd_bank` LEFT JOIN `RSCEmulation`.`users` ON ( `RSCEmulation`.`rscd_bank`.`owner` = `RSCEmulation`.`users`.`id` ) WHERE `RSCEmulation`.`users`.`id` IS NULL;

Actually, that only works when selecting, when I use 'DELETE' rather than 'SELECT *' it throws a syntax error?

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN `RSCEmulation`.`users` ON (`RSCEmulation`.`rscd_bank`.`owner` = `RSCEm' at line 1

[fenway]

DELETE FROM `RSCEmulation`.`rscd_bank` LEFT JOIN `RSCEmulation`.`users` ON ( `RSCEmulation`.`rscd_bank`.`owner` = `RSCEmulation`.`users`.`id` ) WHERE `RSCEmulation`.`users`.`id` IS NULL;

Actually, that only works when selecting, when I use 'DELETE' rather than 'SELECT *' it throws a syntax error?

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN `RSCEmulation`.`users` ON (`RSCEmulation`.`rscd_bank`.`owner` = `RSCEm' at line 1

 

Right, you'll need to start with:

 

DELETE `RSCEmulation`.`rscd_bank`.* FROM ....

 

Regarding the other queries -- INSERT usually can't be optimized.

 

Can't say much about the others without seeing the accompanying EXPLAIN for each one -- which is tricky for delete.

 

You can "set := ... " in the same statement, as a subquery, but that's just a legibility thing.

[Kryptix]

Thanks for your help, I'd still like to sleep after every query though. How could I do this?

[fenway]

I don't know that it's going to help -- it's still "blocking" -- what version do you have?