Page hits counter - Am I overexerting the database?

I've built a simple hit counter on my website (PHP & MySQL, using Codeigniter as my framework).

This is the table I use:

CREATE TABLE page_hits (id INT NOT NULL AUTO_INCREMENT, page_url VARCHAR(350) NOT NULL, ip VARCHAR(11) NOT NULL, hits INT NOT NULL, `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP  NOT NULL, PRIMARY KEY (id));

On every page load, I check if the combination of page_url & ip exists in the table. If it does, I increment the value of hits by 1. If not, I create a new row in the table. The timestamp is there to allow a certain delay between hit counts, so as not to count a page refresh as a new hit.

It all works nicely, but I'm afraid I might be overloading my database... In less than 24 hours, I have over 6500 lines in the page_hits table.

So my question is: What are the risks of having such a rapidly growing table in my database? (performance issues? exceeding database size limitation?)

Solution

Let me start by rewriting your single line SQL command:

CREATE TABLE page_hits 
(id       INT NOT NULL AUTO_INCREMENT, 
 page_url VARCHAR(350) NOT NULL, 
 ip       VARCHAR(11) NOT NULL, 
 hits     INT NOT NULL, 
 date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
 PRIMARY KEY (id))

Now I can see what's there.

Your table is not complex, but it will grow quickly. This will not be a problem, as long as you don't do anything with it. In other words: Adding rows to the table is not a problem, even if you have a million rows.

However as soon as you start to query this table you will find it gets slow very quickly. You've forgotten to add indexes.

How do I add indices to MySQL tables?

Secondly, you could think about normalizing your table and get rid of unneeded info. For instance these three smaller tables:

CREATE TABLE page_hits 
    (id         INT NOT NULL AUTO_INCREMENT, 
     page_id    INT NOT NULL, 
     client_id  INT NOT NULL, 
     hits       INT NOT NULL, 
     PRIMARY KEY (id))

CREATE TABLE pages 
    (id       INT NOT NULL AUTO_INCREMENT, 
     page_url VARCHAR(350) NOT NULL, 
     PRIMARY KEY (id))

CREATE TABLE clients 
    (id       INT NOT NULL AUTO_INCREMENT, 
     ip       VARCHAR(11) NOT NULL, 
     date     TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 
     PRIMARY KEY (id))

Here page_id refers to the pages table, and client_id refers to the clients table. You will have to add the indexes yourself. I would get rid of the date column, and solve this problem with a cookie. Note that the new tables can easily be expanded to contain more info without getting too big too quickly.