creating a counter

dominant

I have created a counter that counts any visitor view my web page.

the problems is that the table which holdes the statistics is getting bigger and bigger. I have create some indexes, however.

Now what should i do if the size of the table makes my page slower while it ls loading?

Weedpacket

Not having seen your schema or what DBMS you're using, I don't really see why your tables would be getting bigger and bigger - surely the number of pages is staying pretty much the same?

drawmack

If you just want a simple counter just use a table that have the page and the number of times it's been loaded if you're using the info for something statistical then create this other table and use it for dislaying the counter and use the current table only for stats.

dominant

When a user access my page then my script checks its ip and if he hasn't accessed my page the an given date then it executes an INSERT statement with the visitorid auto_increment, date and ip.

I use Mysql for my counter

superwormy

Well... thats not really a counter, thats a log file. I would use Apaches logs for this instead... BUT...

You could think about emptying the table each month, or archiving it each month or something.

You should also note that IP addresses are an AWEFUL and UNRELIABLE way to check if a hit comes from someone who has already visited your site. IP addresses change all the time, and can be had by more than one person if they're behind a router / NAT / Proxy.

dominant

Well, what do you suggest ?

superwormy

cookies would be my choice... but nothing is 100% reliable.

dominant

..and what is the best mthod to store my stats if a table getting bigger and bigger?

superwormy

Originally posted by superwormy
Well... thats not really a counter, thats a log file. I would use Apaches logs for this instead... BUT...

You could think about emptying the table each month, or archiving it each month or something.

I already made my two suggestions...

dominant

Definitely i have to implement something that achive a huge table somewhere in my database and rebuild the same table with null records.

One question of mine is : should i create a daemon for that reason which checks the date and do the job or should i run the script manually?

drawmack

If the table is indexed properly it should be able to get very large before there is an appriciable difference in load times.

dominant

I used an index on two fields that participate in my where clause, ip and date.

I deleted 2-3 rows and now i want to rebuild my index.

Do you know how to rebuild it?

drawmack

if you are using mysql you want to repair and optimize the tables. The syntax is in the manual

dominant

I have read that a simple null ALTER TABLE can do the job but i am wondering how that can be done, i mean rebuilding unless we drop and recreate a table.