[RESOLVED] Most efficient way to UPDATE

LordRogaine

I am using extremely large tables that are very actively used by members of my site. I do have a few indexes on my table. I know that indexes cut down on how long it takes to retrieve information, but they also slow down the process of UPDATING or INSERTING into that same table. I want to update a particular member's account which would require that I UPDATE about 12 fields in the table at once. I find that every now and then my table is locking up. My question is, which is more efficient to do an UPDATE ? Would I make 12 individaul UPDATES that change one field each (below) or would I make one UPDATE with 12 field changes (below). I know that the latter is probably more efficient and and quicker by itself. But in av ery busy environment, which is the most effcient with a VERY busy site and least likely to lock up the table?

mysql_query("UPDATE profile SET sex='$sex' WHERE username='$username'");
mysql_query("UPDATE profile SET country='$Zcountry' WHERE username='$username'");
mysql_query("UPDATE profile SET state='$state' WHERE username='$username'");
mysql_query("UPDATE profile SET age='$age' WHERE username='$username'");
mysql_query("UPDATE profile SET height='$height' WHERE username='$username'");

mysql_query("UPDATE profile SET sex='$sex', country='$Zcountry', state='$state', age='$age', height='$height', age='$age', estimatedworth='$estimatedworth', yearlyincome='$yearlyincome', occupation='$occupation' WHERE username='$username'");

alimadzi

Drum roll, please.... the winner is.... door #2!!

One query will always be more effficient than 12 because the database only needs to find the correct row once instead of 12 times. Also make sure you have an index on the 'username' column (a unique index if that's appropriate).

etully

Of those two options, I would say that the second one is faster (because you don't need to open as many connections to the D😎... maybe even 3-4 times faster... but it still might be 1000 X slower than what you need. In other words, you're on the right track in that you're looking for a solution but it's a moot point because this tiny 3-4 times faster is only a small incremental improvement. Even if it works for you today, you're only pushing the problem out six months when you'll have to deal with it again.

DB optimization is a large topic and way outside the scope of what you will normally find in these kinds of forums. I'm afraid that you're going to have to make a serious investment in time or money (or both) to put yourself in a situation where it's Ok if your site grows and your DB is still running smoothly.

Off the top of my head, here are a few paths I would look into:

Hire someone from MySQL to look at your database design. They will make some improvements and you will be able to grow your site 2-10 times bigger than it is now and you will easily be able to handle that traffic on the same hardware you are currently using.
Google for high performance MySQL. O'Reilly has a good book on how to optimize your queries, tables, and databases. There are times when myisam is better and there are times when inodedb is better - you can't guess, you need to choose a table format deliberately.
Put MySQL on a separate machine - not the same machine as your web server. Get mirroring raid, this will speed up your disk access times.
Run lots of tests to find out exactly where the problem lies. It doesn't make sense to get RAID when the problem is CPU - and vice versa. It doesn't make sense to try to optimize queries when the DB table design is the problem and vice versa.

The idea is that you want to put yourself in a situation where your database is running at about 5% capacity so that you can easily handle tons of extra traffic. If you are at 98% load right now and you bring yourself down to 93% load, you have only postponed dealing with the problem.

nemonoman

Assuming username is an indexed column, it is very unlikely that any other UPDATE method would be faster than the query in option 2 (update all fieds in the record with one statement).

SELECT statements (and UPDATE and DELETE statments that SELECT records for update/deletion) do present opportunities for optimization

However SELECTs based on reference to a single indexed column of unique values is going to be about as fast as they get. So all this blather about optimization, while Nice to Know, is really irrelevant in this instance.

Database locking as described would suggest a rather nasty server problem. If you are running these queries on a shared server, particularly one that is overloaded, hosted by a company with pre-intelligent db techies, that would certainly suggest a likely problem area.

Tell us about the server this is running on...

etully

nemonoman wrote:
So all this blather about optimization, while Nice to Know, is really irrelevant in this instance.

Actually, this is the one instance where it's relevant.

He clearly has a successful site and sites like that tend to get more successful. Our company found that a few dozen of our complex queries were hogging the vast majority of our server's resources which caused the thousands of other simple queries to get slowed down. By optimizing these few events that happen only once a minute, we sped up the rest of the queries that happen hundreds of times a minute more than three fold. Pages that were taking 10 seconds to load (that had nothing at all to do with the complex queries) dropped to about 2.5 seconds to load.

"For every complex problem, there is precisely one simple solution that is wrong." Assuming that he can solve this problem with a simple 3 minute fix is probably giving him an unrealistic expectation. Oh sure, a little patch here or there might give him 3% more performance today but it doesn't fix the core problem.

Of course, all the advice in this forum is free, everyone has an opinion, etc.

alimadzi

Well said, etully. Time spent optimizing queries is rarely time wasted...

nemonoman

Please excuse me for pointing out that the actual UPDATE that was posted (option 2 in the original post) cannot be optimized further. Nor is it very likely that the multi-update version (option 1 in the post), although busier, should cause a reasonably loaded database to "lock up".

I surmise ... and I bet I surmise correctly...that this poster's site is running on a shared server that is probably overworked, and managed by techs who are probably also overworked and likely underpaid. The dozens of databases that are likely running on this server are probably built by persons as relatively clueless about optimization as the original poster.

If that is the case ... and I'll bet it is ... this poster hasn't got a lot of options. He can't optimize HIS query beyond option 2. And when his db still locks up (and it will) what's the next suggestion? Try to contact all the other users who are gumming up the works with less optimized queries?? Asking them to EXPLAIN their queiries, index columns, etc. etc?

alimadzi

nemonoman wrote:
If that is the case ... and I'll bet it is ... this poster hasn't got a lot of options. He can't optimize HIS query beyond option 2. And when his db still locks up (and it will) what's the next suggestion? Try to contact all the other users who are gumming up the works with less optimized queries?? Asking them to EXPLAIN their queiries, index columns, etc. etc?

Aye, and therein lies the rub...

etully

The OP described his site as VERY busy and that his tables were very large. My experience with shared hosting companies is that they kick off the server if you are sucking up resources. So while most people use shared hosting, there is a decent chance that he's on a dedicated server.

The update queries he described can't be optimized - we agree on that - but he may have some other page (in the checkout or the admin, maybe) that runs some insane query that joins 11 tables. And if that query in the checkout is run once a minute, it can slow down the 1000 page views of the home page that perform extremely simple queries. So... finding the source of the problem isn't just about looking at the UPDATE statement that he showed in his question. And the only way he can find that is by learning about (or spending money on) optimization.

Lastly, my post had a message which was: If your site is very successful (financially or not) and you want it to survive, then you have to spend some real time and money on this problem. If he is on shared hosting (I'm not as convinced as you are) then my post about optimization sends a pretty clear message that shared hosting isn't going to work for him anymore.

alimadzi

Question for OP (original poster): Have you looked into logging slow queries?

LordRogaine

I'd like to thank everyone that contributed to answering this question. To answer one question, I am on my own dedicated server. I asked my original question because I remembered reading somewhere that even though the most efficient way to write the code would be to have one UPDATE statement with 12 fields affected, it may not necessarily be true of a large and very busy site where more UPDATE statements with less fields being affected might be the better way to go without locking the table or slowing it down. As it turns out that wasn't the cause of my problem. I had a query that joined three tables together which caused the problem. When there were less members on the site it worked perfectly, but as the site started to grow the site started to slow down. The solution was to do a rewrite of that join statement so that only two tables were joined and I looped the results through another query that contained the third table. This freed everything up immensely so that everything is running perfectly.

Thanks again to everyone that contributed.

Best regards,
Lord Rogaine

MarkR

LordRogaine wrote:
which is the most effcient with a VERY busy site and least likely to lock up the table?

If it's a MySQL MyISAM table, any update locks the table briefly. Do the least updates you can. If you are updating multiple columns, check that their values have actually changed - this may improve things somewhat.

In general you should not try to optimise a single query unless you know that it's that causing the problem. In order you should:

Get some performance monitoring on your production systems - at the very least, enable the mysql slow log.
Get a performance test environment - this means the same hardware (or as similar as possible) as your production systems and some means of generating traffic. There are plenty of tools to do this, some free (Apachebench) others commercial - some VERY expensive. Your performance test database should have the same amount and type of data as your production DB - if taking a copy of the production DB is either difficult or impossible (e.g. for privacy reasons), generate synthetic data.
Get profiling data on individual queries, either from production (be aware that profiling will slow your application down further!) or from the afore-mentioned load test system
When you have a proposed fix, try it out on your performance test system vs the previous code, to validate that it makes a significant difference.

Some tips:

Don't add indexes willy-nilly. Measure the storage requirements, time taken to create, load on server etc. Remember that indexes slow down inserts, updates etc, even if they may speed up SELECTs.
Don't do any optimisation if your profiling data don't show that it works. Don't needlessly optimise your code. If you do an optimisation and find it worthless (after testing), revert it.
DO re-run all testing you have (i.e. correctness, acceptance) after any optimisation. It's easy to create bugs.

Mark