locking tables with multiple queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

locking tables with multiple queries?

Post 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.
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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.
yum-jelly
Forum Commoner
Posts: 98
Joined: Sat Oct 29, 2005 9:16 pm

Post 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
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

CONCAT :arrow: concatenate, to join two or more pieces of information together to form a new single piece.
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post 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.
Extremest
Forum Commoner
Posts: 84
Joined: Mon Aug 29, 2005 12:39 pm

Post by Extremest »

Does anyone know why it would take just as long for the one query to run as it does for the three?
Post Reply