CODEWITHSUNDEEP
mysqloptimizationselect
Jerry2
Jerry2

Reputation: 3085

Mysql UPDATE before first checking if necessary or just UPDATE?

I'm using mysql to update a field in a table when a condition is met...

Should I first do a SELECT to see if the condition is met or do I just try to use UPDATE every time, because if the condition is not met, nothing happens.

To be concrete, here is my SELECT:

SELECT * FROM forum_subscriptions 
WHERE IDTopic=11111 AND IDUser=11111 and status=0

I am checking here if I am on forum topic 11111 and if if I (user ID 1) is subscribed to this topic and my status on the subscription is 0 (that means that he didn't yet get email about new post in topic)

So when this is met do:

UPDATE forum_subscriptions SET Status=1 where IDTopic=11111 AND IDUser=1

Now I am wondering, I always do a select here to query if a user is subscribed to this topic and he has a status that he visited that topic before so any new posts will not trigger new email notification. When he visits the page again, the update is triggered that resets the visit so any new posts will again send him email.

So select is made on every user if he is subscribed or not to test the subscription. Update is made only when necessary.

Is it better to just use the update? To try to update on every page, if he is not subscribed to the topic it will not update anything.

How fast is update that doesn't produce any valid data? How is it made internally, how does update find if there is any record, does it select and then update? If so it would be better to only update because I would achieve same thing without any slowdowns. If the update is more expensive than select I should try to check first and then update if necessary.

This example is a real life example, but the logic behing this update/select is really what I am interested because I do find this kind of a problem more often.

Thanx

UPDATE: Thanx both guys, but I do not see on your links if UPDATE is locking even without results or not. As you gave different answers I still don't know what to do.

The subscription table really doesn't need to be myisam, I could change it to InnoDB because I don't have a need to fulltext it. Is this a good solution, to only use update and change this small table to inno? Does mixing table types have any drawbacks?

Upvotes: 2

Views: 1982

Answers (2)

Johan
Johan

Reputation: 76567

You just do the update, with no previous select:

UPDATE forum_subscriptions SET Status=1 where IDTopic=11111 AND IDUser=1

If the conditions are not met, update will do nothing.
This update is very fast if you have an index on status and IDtopic and IDuser!
An empty update is just as fast as an empty select.

If you do the select first, you will just slow things down for no reason.

If you want to know how many rows where updated do a 

SELECT ROW_COUNT() as rows_affected

After doing the update, this will tell you 0 if no rows where updated, or the number of rows updated (or inserted or deleted, if you used those statements).
This function is ultra fast because it just has to fetch one value from memory.

Workarounds for table locking issues

See here: http://dev.mysql.com/doc/refman/5.5/en/table-locking.html

Upvotes: 2

The Scrum Meister
The Scrum Meister

Reputation: 30111

A potential side affect of always calling the UPDATE is the locking that needs to be put to insure that no other connection modifies these rows.

  • If the table is MyISAM - a lock will be places on the he entire table during the search.
  • If the table is InnoDB, locks will be places on the indexes/gaps.

From the Docs:

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row

Upvotes: 1

Related Questions