REPLACE DELAYED usage

[giveasyouget]

Tweet

Hello guys,

Been updating my mysql product database and things seem to be going a lot slower than they ever have before. Nothing's changed my end so I guess it's due to stress on the VPS server elsewhere, and I'm loathe to delete the fulltext index to get things done faster as it'll cripple teh website.

Been reading about INSERT DELAYED, REPLACE DELAYED etc and it states in the mySQL manual that it holds queries in memory until it has the opportunity to perform the operation. Since product updates can often run into the 100s of MB has anyone had any experience of whether using DELAYED for such a procedure either overloads the server (too much to remember every row) or slows it down by taking up too much memory?

Or even for busy sites, does anyone have any experience of whether these DELAYED queries can just sit there forever due to there never being an opportunity to update?

Or am I being silly and should I be putting all the new info into a seperate table, creating the index and then swapping that table for the old one?

Cheers,
Jonny

[Rich]

Replace Delayed shouldn't have any major drawbacks. The advanatge of it is that your update script will finish quicker, the downside is it will mean the update could take a bit longer to actually apear on the site.

The documentation does say that it could take forever for a delayed update to actually take place, but since product databases are mainly read and probably only written to by the update routine that shouldn't be a problem. It is much more likly that its the fulltext index that is starting to bog down.

Creating a new table would probably be quickest if you are not using autnumber columns to point to products (as these would change).

The other thing to do is make sure you are inserting products in batches rather than one query per item as that is massivly faster. i.e do
INSERT INTO table VALUES ('name1','desc1'),('name2','desc2'),('name3','desc 3');
rather than
INSERT INTO table VALUES ('name1','desc1');
INSERT INTO table VALUES ('name2','desc2');
INSERT INTO table VALUES ('name3','desc3')

See speed of insert statements which also applies to replace statements.

[giveasyouget]

aha, i hadn't spotted stacking them like that was faster, i'll try that too.

Does this work with DELETE queries too, they seem to be the slowest ones running?

[Rich]

There isn't much detail on the 'speed of delete statements' page but, assuming parsing the query has a similar overhead to insert statements then
DELETE FROM table WHERE id IN('a','b','c');
should be quicker than
DELETE FROM table WHERE id='a';
DELETE FROM table WHERE id='b';
DELETE FROM table WHERE id='c';