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