I'm a bit new to stored procedure writing in MySQL and am stuck. I need to write a 2 line procedure. Line 1 will execute a query which updates a database table. Line 2 will update another table. I need to execute line 2 only if line 1 updates at least one record. It's this last conditional where I get stuck.

Here's an example:

UPDATE invoice SET status='Paid' WHERE invoice_id=1 AND status<>'Paid' LIMIT 1;
//This should only execute if the line above changed 1 line.
UPDATE account SET num_invoices_paid = num_invoices_paid+1 WHERE account_id=1 LIMIT 1;

NOTE: Those aren't the actual queries, which are more sophisticated, but should give the idea of what I'm trying to do.

It's pretty common to write batches that include code like:

If Exists (Select ID From Table Where Condition = True)
   Update Table Set Column = New_Value

...

You look for rows that would be updated before running the second update... Another way to do it would be:

Declare @cnt int
Select @cnt = Count(*) From Table Where ...

Update 1

If @cnt > 1
   Update 2

Are you saying that the only way is to write an explicit query to determine if the row was updated? There's no equivalent to mysql_affected_rows()? That's what I was looking for and where I got stuck.

I discovered the answer. It's the ROW_COUNT() function. For those others who were lost like I, here is the code which worked:

SET updated_rows = ROW_COUNT();
IF (updated_rows = 1)

Row count isn't always reliable. It's much better to do something like Forrest wrote, but inside of a transaction.

Thanks, but these stored procedures are for a large table. Running a database query to go through and determine if a change has occurred will be a comparatively-expensive option. Is there nothing better?

It's standard practice. A small exists query should take milliseconds. Once you've run the first query, everything you need is cached (query plan, data pages, etc).

Realized I should probably point out the reason Forest and I got to the almost same answer without discussing the question amongst ourselves. Doing a check and then performing an operation based on its results is standard operating procedure for database code. Again I'd caution to do this inside a transaction to prevent another connection from changing the data between when you get an answer and make a decision based on it, but it's the same concept.

It's a shame row count isn't reliable, but the company I work for is a consulting and custom development shop, and we've charged to fix apps that weren't working, in part because they used row count as a shortcut. So I'm especially gun shy toward that function. Tran count is another unreliable one.

I did some testing as follows. The results are below. Thank you 2 for your guidance.

PHP Code:

<?php
$start_time = microtime(true);
$db = new MySQLi('localhost','test','test','test');
/* Populate with data */

$alphabet_consonants = array('b','c','d','f','g','h','j','k','l','m','n','p','q','r','s','t','v','w','x','y','z');
$alphabet_vowels = array('a','e','i','o','u');

for ($counter=0;$counter<2000000;$counter++) {
  $first_name = ucwords($alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)]);
  $last_name = ucwords($alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)]);
  $username = ucwords($alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)]);
  $password = ucwords($alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_consonants[rand(0,20)].$alphabet_vowels[rand(0,20)].$alphabet_consonants[rand(0,20)]);
  $sql_query = "INSERT INTO `user` ( `first_name` , `last_name` , `username` , `password` ) VALUES ( '".$first_name."', '".$last_name."', '".$username."', '".$password."' )";
  $db->query($sql_query);

}

$db->query($sql_query);
echo $db->error.'<br />';
echo (microtime(true)-$start_time);
?>

This populated the database with 2 million rows of random data. I then edited row 83,201 to have the password MatchingPassword to ensure only 1 match. I then created 2 stored procedures:

CREATE PROCEDURE testExists()
BEGIN
IF EXISTS (SELECT * FROM user WHERE password='MatchingPassword' LIMIT 1)
  THEN
    UPDATE user SET username='Jeremy' WHERE password='MatchingPassword' LIMIT 1;
END IF;
END;

and

CREATE PROCEDURE altTestExists()
BEGIN
UPDATE user SET username='Jeremy' WHERE password='MatchingPassword' LIMIT 1;
END;

I then executed each procedure with and without the "LIMIT 1"'s present. Here are the results:

PROCEDURE: testExists
0 w/ "LIMIT 1" --> 47.268954992294 seconds
1 w/ "LIMIT 1" --> 7.708044052124 seconds
2 w/ "LIMIT 1" --> 0.29384803771973 seconds

The scenario with 1 "LIMIT 1" had the LIMIT 1 on the UPDATE query.

PROCEDURE: altTestExists
w/o "LIMIT 1" --> 38.955604076385 seconds
w/ "LIMIT 1" --> 8.0809271335602 seconds

I was totally shocked by the 2 w/ LIMIT 1 results as it seems to understand, per se, that the row to be updated matches that of the EXISTS query. I even ran it a couple more times just to be sure I wasn't messed up. Totally cool.

Anyway, thanks again. I thought the results of these tests may be helpful for someone else.

Oh, I guess I should point out 3 things:
1) I know that the 2,000,000 row insert could be done w/o all of the INSERT (i.e. via 1 insert), but didn't care to optimize.
2) The structure of that table is here:

CREATE TABLE `user` (
  `user_id` mediumint(8) unsigned NOT NULL auto_increment,
  `first_name` char(35) NOT NULL,
  `last_name` char(35) NOT NULL,
  `username` char(35) NOT NULL,
  `password` char(128) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM;

3) If you see something other than the garbage data which could have been done better, I'm all ears.

I'm not sure I understand your test results, but I'm glad you got it working? I hope the performance is in line with what you have in your time budget?

Basically, the testExists procedure as shown in the code above was 20 times faster than any of the others, so yeah, that's really great optimization.

I thought I'd share a correction to my knowledge. I interpreted the results above to mean that I should use LIMIT 1 in all cases where I was selecting or updating. When using a unique or primary key, this is not necessary and provides no optimization. In the test above, I was searching on a non-indexed key which explains the optimization. It'd have been prudent of me to rework this stuff with an index on the searched column and test that, but now I don't recall what I was building this for.

Just thought I'd share that correction in case anyone else garnered the same wrong information as I did.

SQL Server does some great optimizations against anything that's constrained unique. When it finds a match, it stops looking, so on average it will have to evaluate half the rows.

I can't speak to MySQL, but with some appalling exceptions, most of what applies to one RDBMS applies to all of them. Which is why I tend to answer MySQL questions with MS SQL Server answers, because conceptually they aren't all that different.

That said, a unique constraint or unique index provides better performance by far. A Top N (which is what Limit N is) implies a sort, and if you look in the query plan, you'll see one whenever the data access mechanism doesn't have a sort provided. (In other words, if the database is getting the data through an index that's sorted, vs by reading the table directly.) The sort applies to all rows that match your predicates (where and join clauses) to figure out which N rows to provide.

You know, LearningNewbie, I've always wondered about sorting my indices. I haven't tested it, but have read where sorting the index before searching could be helpful. The warning that came with that is that it locks the table (I believe) while the index is being created, so hiccoughs may result during such a procedure.

In Microsoft land, online indexing became a reality in (late) 2005. So I would expect the table to be locked while you create an index in pretty much any RDBMS other than Oracle. I'm sure future versions will address that, but until then, that's the situation, and you're right. That said, I've seen about 10 million rows indexed per minute, so locking a table briefly shouldn't be a problem in most cases.

Do you remember this game, as a child?

Joe: I'm thinking of a number between 1 and 100. Try to guess it, I'll tell you if it's higher or lower.
Ann: Is it 50?
Joe: No, it's higher.
Ann: Is it 75?
Joe: No, it's lower.
Ann: Is it 63?
Joe: Nope. Higher.

Anyway, I won't type out the full thing, but you guess halfway across the range, get some feedback, then half the new, narrower range. You can get to the right answer out of 100 choices in 6 steps, instead of by doing 100 comparisons, this way.

That's how an index works. It's always sorted (at least in SQL Server - if you don't tell it a sort order, it defaults to ascending) and because of this, it can do a binary search. This lets it "seek" a particular value, instead of "scan"ning all values in the list, to get to the slot where the right one belongs. (I say belongs because you can delete rows from the table, so the value you want won't always be there.)

When you have an index, say on a numeric column and you add row #63, your database engine will update the index and put the new value in the slot it belongs based on the sort order. If it's not able to do that, it will fail the insert. (Same goes for update and delete queries.) The index will never be out of sync with the data. This is why they slow DML against the table, and are considered a double edged sword. But a nonclustered index is just a list of values, and for each of those a list of row IDs, so this kind of maintenance is usually very fast.

Thanks for those details LearningNewbie. I'm quite familiar with the benefits of a binary search or sorted data. After reading your post I decided to find out where I read that bit. This is what I found:

Originally Posted by MySQL Database Design and Tuning, pgs 236-237

If you frequently retrieve large ranges of indexed data from a table or consistently sort results on the same index key, you might want to consider running myisamchk with the --sort-records option...Alternatively, you can combine the ALTER TABLE statement with an ORDER BY a particular column option to achieve the same results.
...
From this point forward, all rows in the table will be stored in [that] order until new rows are added. If you want to make this sequencing more permanent, consider running this command periodically.

So, I guess that MySQL is behind the times in this implementation -- or I'm reading something wrong.

To add context to the above quote, they are discussing a table indexed on 3 columns, but no primary key. I did see an example, however, which may illuminate things:

Originally Posted by MySQL Database Design and Tuning, pgs 124

...a B-tree index, which is the index format MySQL uses for all of its data types...and storage engines.

It then discusses how it fills holes in non-compressed indexes using a round-robin approach which is what makes me wonder if the PK sorting is maintained.

I'm thinking of creating a table with a PK, inserting a bunch of rows, deleting random rows, and inserting some more rows and then look at the actual index file, but I'm not sure where they are or if I'll even be able to read them -- could be binary for all I know.

Actually that first quote you posted makes absolute sense to me.

In SQL server, we have clustered and non clustered indexes. A clustered index means that the data in the table is written to disc in the same sort order as the index itself. A non clustered index is sorted, but the table data probably isn't sorted in the same way. Which means (1) when you binary search a non clustered index, and get to the result you want, there's 1 more step getting from the index to the table data, and (2) you can only have 1 clustered index per table.

It sounds like ALTER TABLE + ORDER BY is the same concept as a SQL Server clustered index. (Or maybe that MyIsamChk is, or both?)

So, are you saying that even when the data isn't stored sequentially, that a routine is applied before querying the index which allows for a b-tree search (the extra step you mention)?

The ALTER TABLE + ORDER BY and myisamchk's mentioned above are supposed to do the same thing. I just wonder how long that would take versus allowing the extra step for non-sorted indices.

So, if the data in the table isn't sorted in the same way the data in the index that's being used is, then some overhead comes in. At least in MS land. The index is used to find the rows that match a where or join. Now it knows which rows, but needs to load them in memory. When the index and table are sorted in the same order, the leaf node of the b tree is the data row itself, but in all other indexes, the leaf node is a bookmark, or row ID, or physical location on disc where the row lives. In SQL Server, when you see a Bookmark Lookup or RID Lookup in a query plan, it's a very bad thing.

You can define a clustered index (or run an ALTER TABLE + ORDER BY command) against a non unique column or set of columns. In SQL Server, this adds a small overhead as a "uniquifier" is added to each row. I think this is a 4 byte int value, randomly generated, so that "JONES" and "JONES" (or any 2 identical values) always sort the same way, keeping the table and index in sync.