Performance of INSERT... ON DUPLICATE KEY UPDATE

[Jesus Inzunza]

I have a listing website with thousands of ads. I keep a visit counter on each ad, but I want to implement a visit counter per day, per ad. My table design to achieve this is the following:

CREATE TABLE hitsPerDay (
adId VARCHAR(16) NOT NULL,
hitDay DATE NOT NULL,
numberOfHits SMALLINT(4) UNSIGNED NULL,
PRIMARY KEY(adId , hitDay),
INDEX hitsPerDay_index3554(adId)
)
TYPE=InnoDB;

When someone checks an ad, it inserts a new record or updates it if a duplicate on the adId/date is detected:

INSERT INTO hitsPerDay VALUES ('ad2332', CURRENT_DATE(), 1)
ON DUPLICATE KEY UPDATE numberOfHits = numberOfHits + 1;

Everything works great in my tests, but given the high volume of ads, and the number of times each ad will be consulted, I am afraid the table will reach hundreds of thousands of records in a few days.

Is this the best approach? Will there be performance issues if the table reaches, say, 1 million records? Any advice will be appreciated.



Edited 1 time(s). Last edit at 03/09/2011 12:15PM by Jesus Inzunza.