CODEWITHSUNDEEP
phpsqlmysqldatabase
Vincent
Vincent

Reputation: 6188

Query efficiency

$_query = 'UPDATE ' . TABLE_A1 . ' SET active=0 WHERE user_id IN ( ';
                foreach ($newUsers as $newUser) {
                    $_query.=$newUser. ' ,';
                }
                $_query = rtrim($_query, ',');
                $_query .=')';

Is this a performant way of editing multiple table entries at once? Are there faster ways?

thanks

Upvotes: 1

Views: 145

Answers (3)

Heiko Hatzfeld
Heiko Hatzfeld

Reputation: 3197

You could add another contraint to the query, which could speed it up a tad more, but that depends on how you get your users...

If you have some users that are already "active=0", thou you could exclude them in the query by adding

.... where active<>0 and user_id in (...

This will prevent updates on fields that already have the correct value. I had a similar query on a system oncce, and it was updating millions of records which alreay contained the correct information...

Upvotes: 1

Esteban
Esteban

Reputation: 884

The UPDATE is fine. You can make your PHP a bit more elegant by using an implode instead of looping through that array.

i.e.: 

$_query = 'UPDATE ' . TABLE_A1 . ' SET active=0 WHERE user_id IN (' . implode(',', $newUsers) . ');'

Upvotes: 1

Thariama
Thariama

Reputation: 50832

Yes, this is very performant. I do not know of any faster way.

Upvotes: 1

Related Questions