Page 1 of 1

locking tables with multiple queries?

Posted: Sat Nov 05, 2005 10:06 pm
by Extremest
I have a myisam table with 2 columns. The primary key is the first column and is unique. I have php setup to insert new data into them. If it fails because the key is already there then it will do a select to grab the contents of the second column for that key. Then it will add the new data to the old and update the second column. I usually do around 3.5 million entries per update and it takes around 2-3 hours for it to do it. I was wondering if I like the table with write first then have it do it if it would be faster. Wasn't sure if you could do an insert, select, and update all in lock write or not. I figured it would keep the server from locking the table on each query and save some speed.

Posted: Sun Nov 06, 2005 11:13 am
by Extremest
Also if I lock the tables before the update will it also update the keys. Meaning it will still find the duplicate keys right if they are new ones. I tried it on a dev table and it seems to help keep the flow stable instead of the amount of queries bouncing all over the place.

Posted: Sun Nov 06, 2005 7:47 pm
by yum-jelly
If your just wanting to add data to a row that could exist then just use INSERT .... ON DUPLICATE KEY UPDATE!

Example!

Code: Select all

INSERT INTO table_name VALUES ( 'key', 'new junk to add' ) ON DUPLICATE KEY UPDATE column_two = CONCAT( column_two, ', new junk to add' )
if the key exists it updates column_two adding *, new junk to add* to the data already in column_two

yj

Posted: Mon Nov 07, 2005 7:43 am
by Extremest
ok I am going to give that a try. What does concat do? I tried searching at mysql and couldn't get a real good answer. I am still new to mysql. I did the lock tables and it has made the update run 6 times faster. Hopefully with this it will make it even faster.

Posted: Mon Nov 07, 2005 7:59 am
by feyd
CONCAT :arrow: concatenate, to join two or more pieces of information together to form a new single piece.

Posted: Mon Nov 07, 2005 4:38 pm
by Extremest
ok I tried it but time wise it takes just as long to have it do all 3 queries as it does to do the one. Not sure why on that.

Posted: Wed Nov 09, 2005 5:52 pm
by Extremest
Does anyone know why it would take just as long for the one query to run as it does for the three?