MySQL Poor performance INSERT or PHP PDO

Question

Usually when a user comes to my website he 'subscribes' to around an average of ~50 events at once, which we save in a mapping_table that links many users to many events. maping_table has around 50k entries, growing by several hundreds each day.

INSERTing those ~50 lines in the mapping table takes around 10 to 20 seconds which makes my website appear very slow.

I reach a point where I can't figure out how to improve the following PHP or SQL in order to lower the INSERT time as it seems pretty basic.

Here is the SHOW CREATE statement :

    CREATE TABLE `mapping_table` (
     `user_id` char(21) NOT NULL,
     `event_id` char(21) NOT NULL,
     `update_id` char(10) NOT NULL,
     PRIMARY KEY (`user_id`,`event_id`),
     KEY `event` (`event_id`),
     CONSTRAINT `mapping_table_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users_table` (`user_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
     CONSTRAINT `mapping_table_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `events_table` (`event_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

My php script for the insert part :

    $update_id = 'RandomString';
    $conn = new PDO($pdo_serv, $db_user, $db_pwd);
    $stmt = $conn->prepare("INSERT INTO `mapping_table` (`user_id`, `event_id`, `update_id`)
                    VALUES (:uid,:eid,:upd)");

    //GO THROUGH EACH USER SUBSCRIBED EVENTS AND INSERT THEM
    foreach($subscribed_events AS $event) {
        $stmt->bindParam(':uid', $userid);
        $stmt->bindParam(':eid', $event->id);
        $stmt->bindParam(':upd', $update_id);
        $stmt->execute();
    }

I'm open to any suggestions.

Additional information :

If I microtime each insert I get an average of 300ms to 1sec per insert.

I have tried deleting the UNIQUE KEY, then all FOREIGN KEYS, then all INDEXES but those did not make any difference in performace.

try to add an `AUTO_INCREMENT` numerical primary key to the relation table and try to du all `INSERT`s in a single transaction. — DerVO, Mar 10 '16 at 13:01
I'll try the Batch `INSERT` now and I'll let you know. I thought PDO `prepare()` and `execute()` were really efficient in multiple successive queries though, hence my chosen design. — Fredovsky, Mar 10 '16 at 13:09
See here a few examples http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query — Mihai, Mar 10 '16 at 13:16
Other issues... Don't use `CHAR` unless the column is really fixed length. Instead use `VARHAR`. — Rick James, Mar 11 '16 at 03:40
Check for errors. Batched inserts, though much faster, are at more risk for deadlocks. — Rick James, Mar 11 '16 at 03:40
@DerVO - an `AUTO_INCREMENT` would hurt more than help in this case. — Rick James, Mar 11 '16 at 03:41

score 0 · Answer 1 · answered Mar 10 '16 at 14:04

Following comments on my questions I modified my php as follow for batch INSERT :

$update_id = 'RandomString';
$conn = new PDO($pdo_serv, $db_user, $db_pwd);

$sql = "INSERT INTO `mapping_table` (`user_id`, `event_id`, `update_id`)
                VALUES ";
$values = "";

//GO THROUGH EACH USER SUBSCRIBED EVENTS GENERATE VALUES STRING
foreach($subscribed_events AS $event) {
    $values .= "('".$userid."','".$event->id."','".$update_id."'),"
}

//Proceed batch insert
$values = rtrim($values, ",");
$sql .= $values;
$stmt = $conn->prepare($sql);
$stmt->execute();

This decreased my average insert to less than 10ms per line inserted (as opposed to 300ms to 1sec per line previously). Never thought the difference would be that big. Thanks.

if you watched this question for performance reasons, but want to keep a bit of safety, make sure to call `$conn->quote($userid)` instead of `$userid`. It will slow a little bit down the overall process. Even if you are absolutely sure of you data source, you are not guaranteed this will be the same in 1 month, 1 year, ... So make sure to use it, does not cost very much at the end. — Anwar, Jun 28 '18 at 20:01

MySQL Poor performance INSERT or PHP PDO

1 Answers1