Time cost for an Update

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
aka_eu
Forum Newbie
Posts: 9
Joined: Wed Sep 28, 2005 10:13 am

Time cost for an Update

Post by aka_eu »

Hi,

I have some tables with mores than 50.000 records on each, on a MySQL database. I'm concerning about the times that I need to do some opperations.

For example I have a table with more than 50.000 records on it. I'm doing an update like this

UPDATE table SET field2=2 WHERE primy_key_field=30450

After I do some tests I observed that I need a time from this interval for each opperation

0.00020s < Execution Time < 0.00035s

The problem is that sometimes I have to do more than 10.000 updates at one time, and it takes a lot of time. It's possible to have a less execution time for an update ?

How can I reach a fastest execution time ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

optimize your queries; such as grouping all the id's where the change will be the same for each record and reducing the number of queries you perform.

optimize your table; use indexes where useful (read and understand the output of

Code: Select all

EXPLAIN UPDATE table SET field2=2 WHERE primy_key_field=30450
)
Post Reply