MYSQL&PHP: running an INSERT INTO SELECT query within a PHP while loop, running slow

Question

I'm really new to php and MYSQL, i knew nothing about either a month ago, so please forgive my sloppy/poor code :)

I have the following code within my PHP:

$starttime = microtime(true);
$q_un = 'SELECT i.id AS id
            FROM items i 
            WHERE i.id NOT IN (SELECT item_id FROM purchased_items WHERE user_id=' . $user_id . ')';
$r_un = mysqli_query($dbc, $q_un);
if (mysqli_num_rows($r_un) > 0) {
while ($row_un = mysqli_fetch_array($r_un, MYSQLI_ASSOC)) {
    $item_id = $row_un['id'];
    $q_rec = 'INSERT INTO compatibility_recommendations (
                `recommendation`,
                `user_id`,
                `item_id`)
                SELECT
                    ((SUM(a.rating*(a.compat-80)))/(SUM(a.compat-80)))*10 AS rec,
                    a.user_id AS user_id,
                    a.item_id AS item_id
                FROM
                    (SELECT r.rating AS rating, 
                        c.user2_id AS rater, 
                        c.user1_id AS user_id, 
                        c.compatibility AS compat, 
                        r.item_id AS item_id 
                    FROM ratings r
                    RIGHT JOIN compatibility_ratings c ON r.user_id=c.user2_id
                    WHERE c.user1_id=' . $user_id . ' AND r.item_id=' . $item_id . ' AND c.compatibility>80) a
                ON DUPLICATE KEY UPDATE
                    recommendation = VALUES(recommendation)';
    $r_rec = mysqli_query($dbc, $q_rec);
}
}
$endtime = microtime(true);
$duration = $endtime - $starttime;</code>

The first query selects a list of items that the current user, $user_id, hasn't purchased yet. I then run a while loop on each row (item) that is returned, performing the main query within this loop.

This next query is taking info from the ratings table where the item_id is equal to the current item_id which is being queried, and joins it to a precomputed user compatibility table with a right join.

I then run arithmetic on the ratings and compatibility ratings to form a recommendation value, and then insert the recommendation, item_id and user_id into another table to be called later. There's a 2 column unique key on the (item_id,user_id) columns, hence the ON DUPLICATE KEY UPDATE at the end

So i wrote this code this morning and was quite happy with myself as it does exactly what i need it to do.

The problem is that, predictably, it's slow. On my test DB, with 5 test users and 100 test items and a random assortment of 200 ratings, it's taking 2.5 seconds to run through the while loop. I was expecting it to be slow, but not this slow. it's really going to struggle once more users and items are added. The main problem is on the insert...on duplicate key update part, my disk utilisation goes to 100% and i can tell my laptop's HDD is seeking like crazy. I know I will probably use SSDs in production, but I would still anticipate a major scale issue with thousand of items and users.

So my main question here is: can anyone give any advice on how to optimise my code, or completely rejig things to improve speed. I'm sure that the insert query within a while loop is a poor way of doing this, i just can't think of any other way to obtain the exact same results

Thanks in advance and sorry if i formatted my question incorrectly

Answer 1

I found the answer that I was looking for here

The second query for each item was taking 0.002 seconds for just the select, but then 0.06 seconds with the insert, so i profiled the query and found that "query-end" was taking 99% of the query time. I've set innodb_flush_log_at_trx_commit = 0, but the comments to that answer frown upon it. I don't use transactions however, so are there any consequences/alternatives to this approach? It did reduce my while loop time from 2.5 seconds to 0.08 seconds.

Answer 2

$starttime = microtime(true);
$q_un = "

 INSERT INTO compatibility_recommendations 
 (recommendation
 ,user_id
 ,item_id
 )
 SELECT ((SUM(a.rating*(a.compat-80)))/(SUM(a.compat-80)))*10 rec
      , a.user_id 
      , a.item_id 
   FROM
      ( SELECT r.rating rating
             , c.user2_id rater
             , c.user1_id user_id
             , c.compatibility compat
             , r.item_id 
          FROM compatibility_ratings c
          JOIN ratings r
            ON r.user_id = c.user2_id

          JOIN items i
            ON i.id = r.item_id

          LEFT
          JOIN purchased_items p
            ON p.item_id = i.id
           AND p.user_id = $user_id

         WHERE c.user1_id =  $user_id
           AND c.compatibility > 80
           AND p.item_id IS NULL
      ) a
 GROUP BY a.item_id
 ON DUPLICATE KEY UPDATE recommendation = VALUES(recommendation);

 ";

$r_rec = mysqli_query($dbc, $q_rec);
}
}
$endtime = microtime(true);
$duration = $endtime - $starttime;</code>

For any further improvement, we'd really need to see proper DDLs AND the EXPLAIN for the SELECT above.

Answer 3

See https://stackoverflow.com/a/14456661/2782404

fetch_assoc may be significantly faster than fetch_array, and you should be fetching all at once before you access the values.