0

What's best performance wise, two separate UPDATEs:

UPDATE table1
    SET enabled = 0
    WHERE code NOT IN (
        SELECT id
        FROM table2
    );

UPDATE table1
    SET enabled = 1
    WHERE code IN (
        SELECT id
        FROM table2
    );

Or a single UPDATE with a CASE?

UPDATE table1
    SET enabled = (
        CASE
            WHEN code IN (SELECT id FROM table2) THEN 1 
            ELSE 0 
        END
    ); 

(and let's consider there are no NULL values in the relevant columns).

What would perform best, supposed code and id columns have are indexed?

I can't really tell because I don't have significant tables, and the only difference EXPLAIN gives is that the former version uses the WHERE (but I analyzed only one UPDATE) - I'd say that a plus, but I'm unsure if that's enough.

I'm working with MySQL.

  • @ypercube Done, since I'm mainly interested in MySQL. But I thought that, in this particular case, it is more of a conceptual question. Or, to put it in another way, how do I check the performances without having real data, when having more than one statement? – watery Aug 28 '14 at 10:42
  • I'm not so much into mysql performance, but in sql server the 2nd should perform better. You are only running once through data and you avoid the not in which can badly perform. – JoseTeixeira Aug 28 '14 at 11:06
2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = 1 - ISNULL(B.id);

Why should this work ?

  • If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2
  • If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) because it is not in table2

@ypercube suggested

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = (B.id IS NOT NULL);

GIVE IT A TRY !!!

  • 1
    Also SET a.enabled = (b.id IS NOT NULL); – ypercubeᵀᴹ Jul 30 '15 at 19:23
  • @ypercube That would be more readable and concise – RolandoMySQLDBA Jul 30 '15 at 19:24
0

Performance is a very fluid concept.

  • It can vary according, for example, to the number of rows in your tables (use of indexes or not).

  • It can depend on whether you use InnoDB or MyISAM.

  • It can depend on what else is occurring on the system at the same time.
  • It can depend on your hardware.
  • And it can certainly depend on your RDBMS.

You should run tests for yourself on your own system and then come back to us if you're unsure of something. Run EXPLAIN my_query for both queries for MySQL and compare the results. Is it working well now? If my data increases, will it work well in the future?

Check the docco for other systems.

  • I know; that's why I thought this would rather be a conceptual question (see my comment) - I even said I have no data to run tests -, otherwise I wouldn't have asked it ;-) – watery Aug 28 '14 at 16:45
  • Ah... I didn't correctly interpret "I don't have significant tables". In any case, an EXPLAIN is useless without data, since the optimiser will behave differently depending on the amount of data in a given table - i.e. with little data, it won't bother to use an index if the table is so small that a FTS (Full Table Scan) will zip through it more quickly, as mentioned in my first point. – Vérace Aug 28 '14 at 16:55
0

You should test it on you system and compare the the execution data and plans.

But the database systems I know only optimize statement by statement and not pairs and groups of statements. And for an optimal optimizer that possesses enough information about the data and optimizes statement by statement I would expect that he can create a better plan if the work is contained in one statement instead of distributed over two statements.

For your statement it is possible that the optimizer for each of the three statements reads the whole table table2 and then scans the whole table table1 and changes the values according to the update clause. So the variant with two updates then will need to scans of table table1 and table table2 and only one scan of table table1 and table table2 for the second variant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.