Most efficient way to update multiple values in Mysql using PDO

Question

Given this type of User table structure where you are storing many User values (such as phone #'s, preferences, contact info) in a table:

Table: User ID | Key | Value

$values = [
    1 => 'A value for key 1',
    2 => 'Hello',
    8 => 'Meow',
]

// Update values
$stmt = $pdo_db->prepare('
    INSERT INTO table (UID, KEY, VALUE)
    VALUES            (:UID, :KEY, :VALUE)
    ON DUPLICATE KEY UPDATE VALUE = :VALUE');

foreach ($values as $key => $value) {
    $stmt->bindParam(':KEY', $key);
    $stmt->bindParam(':VALUE', $value);
    $stmt->execute();
}

If you have 150 different pairs, thats 150 queries per update. How would I optimize this code? Would making a giant SQL make the work easier on the mysql side? Should I be looking at changing the structure itself?

Have a look at http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query — John Carter, Sep 04 '12 at 01:21
There really really really isn't any good reason to try to update multiple rows in a single statement. Why do you think this is a problem? Is this really your biggest bottleneck? Did you measure your performance to determine that? This is called "premature optimization" — Gavin Towey, Sep 04 '12 at 02:07

Ivo · Accepted Answer · 2012-09-04T01:30:54.313

2

Take this query as a guide-

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

Allows you to insert multiple records at once.

If you know which records are new, it could speed up to separate those into a plain INSERT - mysql has to spend extra time reconciling the ON DUPLICATE... UPDATE otherwise.

Also think about using UPDATE or REPLACE if you're only updating.

edited Sep 04 '12 at 01:30

answered Sep 04 '12 at 01:14

Ivo

5,332
2
15
18

1

You forgot the `ON DUPLICATE KEY UPDATE` part – Mike Sep 04 '12 at 01:15
@Mike as if Paul wouldn't be able to trivially add that in? – Ivo Sep 04 '12 at 01:18
1

I'm just saying... to keep it in roughly the same format as he has it above. I know you said it's just a guide though. – Mike Sep 04 '12 at 01:19
Does this method have any performance benefits? Also, the keys are added/removed all the time, so the structure needs to be flexible for a huge amount of different keys – Paul S. Sep 04 '12 at 01:22
It has great performance benefits over submitting separate queries in a loop, certainly. – Ivo Sep 04 '12 at 01:24
I thought with prepared statements, it would be just as fast as dumping all rows into one query as you suggestion. Can you expand on your answer? – Paul S. Sep 04 '12 at 01:28
@PaulS. actually, going back over the PDO docs, it can optimise the dbo connection so that simply looping an execute is still very fast. So your original code should still be pretty good. – Ivo Sep 04 '12 at 01:28

Most efficient way to update multiple values in Mysql using PDO

1 Answers1