MySQL - 选择行时更新表

Question

I have the following query:

SELECT name FROM restaurants WHERE city='Kalmar' LIMIT 100

This is working as it should, but now I want to extend some functionality to it. I want to keep count of how many times per day the rows has been selected.

Let's say we managed to select 5 restaurants from the query, I want to do something like this for each one of them:

INSERT INTO statistics (date, restaurant_id, number_of_selects) VALUES('{$current_date}', (The restaurants ID), 1) ON DUPLICATE KEY UPDATE number_of_selects = number_of_selects + 1"

What this code does is to increase restaurants number of selects inside the statistics-table. If the row doesn't exist (meaning, it's the first select) it will create the row.

And now to the problem!
What I could do is to loop all the results in PHP and use the second query. However, this is a performance killer. I'm looking for a way to use both these querys at the same time to get the best performance possible.

Edit: I want to keep the data I select (In this case the name of the restaurants).

Thanks in advance,
Tompa

Answer 1

I would suggest inserting the result first into a temporary table. Then use this table to update the statistics table, before returning the results to php. This will remove the need for a cursor / loop to iterate over the result set (which is what would kill your performance)

So something like:

--Get the restaurants
CREATE TEMPORARY TABLE IF NOT EXISTS tempTable AS
    (SELECT id, name
    FROM restaurants
    WHERE city='Kalmar'
    LIMIT 100
    )

--Update the statistics
INSERT INTO statistics (date, restaurant_id, number_of_selects)
SELECT DATE(), id
FROM tempTable
ON DUPLICATE KEY UPDATE number_of_selects = number_of_selects + 1

--Return data to PHP
SELECT name from tempTable 

I havent checked the syntax etc, but I would expect something like that to work