einav einav - 6 months ago 21
PHP Question

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?)

Answer

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 to add indexes 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.

Comments