Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 06-11-2003, 03:18 AM
futureal futureal is offline
 
Join Date: Feb 2002
Location: Del Mar, CA, USA
Posts: 556
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Efficient PHP/MySQL Page Hit Counter

Hi,

Some time ago, I wrote a simple page hit counting script that used PHP/MySQL. All I wanted was to track the # of hits on a page, and use a timeout value that prevented a user from refreshing to bump the count. I set the timeout to 15 minutes, so it tracks IPs and checks to see if the same IP has accessed that same page within the timeout period, and if so, does *not* count a hit, but re-sets the timeout. Otherwise, it records a hit.

It also uses a "page ID" feature, so I could use the same script for as many pages across the site as I'd like. So on page One, for example, I would call it, counter.php?pageid=1. On page Two, counter.php?pageid=2 and so on. Once again, very simple, and when I create a new page, all I need to do is make a new unique number, and it automatically starts it off at zero.

So all of that sounds good, right? Yup.

However, now there's a problem, one I hadn't previously foreseen. Now that it has been running for awhile, it is having huge performance issues. Observe:


Quote:
mysql> select count(*) from counter;
+----------+
| count(*) |
+----------+
| 807245 |
+----------+
1 row in set (0.00 sec)
Counting the entire amount of rows in the database is still an efficient query. However, here is what happens when we look at a particular page:


Quote:
mysql> select count(*) from counter where pageid=0;
+----------+
| count(*) |
+----------+
| 453526 |
+----------+
1 row in set (6.01 sec)

Ouch. It's killing my page-load times, and it's in dire need of some rethinking. One option, of course, is to create a separate table for each page I want to use, but that's not really an option I'd like.

My next idea is to create a second table, and use it as a counter cache. When a user visits a counted page, the new process would be something like:

- browser records IP/host and pageid
- script checks database for matching IP/timestamp
- if the IP has not visited the page within the timeout period, increment the count in the cache table, and insert a new row into the data table with the ip/timestamp info
- grab the cached counter for this page, and display

So if anybody actually read this far (haha), what do you think? Is there a better way to do this that I'm overlooking? I honestly have not looked at other counter scripts too much, mainly because they were full of fancy user interfaces and other garbage that I don't need or want. I just want something that's simple, effective, and fast, and that I can add new page counts to simply by putting a different number on a page.

Any feedback is welcomed, of course.
Reply With Quote
  #2  
Old 06-11-2003, 01:35 PM
Dean C's Avatar
Dean C Dean C is offline
 
Join Date: Jan 2002
Location: England
Posts: 9,071
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I'm sure that someone posted such a script in here a few weeks ago. Slynderdale?
Reply With Quote
  #3  
Old 06-11-2003, 01:44 PM
filburt1 filburt1 is offline
 
Join Date: Feb 2002
Location: Maryland, US
Posts: 6,144
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Never, ever, ever use * in a query...except, according to the MySQL people, when it's COUNT(*) without a WHERE clause.

Try:
[sql]
SELECT COUNT(some id column with unique values) FROM counter WHERE page = 0;
[/sql]
Reply With Quote
  #4  
Old 06-12-2003, 05:57 AM
futureal futureal is offline
 
Join Date: Feb 2002
Location: Del Mar, CA, USA
Posts: 556
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Yesterday at 09:35 AM Mist said this in Post #2
I'm sure that someone posted such a script in here a few weeks ago. Slynderdale?
I checked it out and that script is basically a less advanced version of the one I already wrote and am running. It still is just inserting a row when a hit is recorded, and then selecting out of the entire set.
Reply With Quote
  #5  
Old 06-12-2003, 06:11 AM
futureal futureal is offline
 
Join Date: Feb 2002
Location: Del Mar, CA, USA
Posts: 556
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Yesterday at 09:44 AM filburt1 said this in Post #3
Never, ever, ever use * in a query...except, according to the MySQL people, when it's COUNT(*) without a WHERE clause.

Try:
[sql]
SELECT COUNT(some id column with unique values) FROM counter WHERE page = 0;
[/sql]
I tried messing around with enumerating the query but the results were not much faster, if any. At best the average dropped from ~4 seconds to ~3 seconds when selecing about 400,000 out of 800,000 records.

If anybody has experience in writing a counter designed to handle > 1 million hits, I'd love to hear about it.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:01 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.07151 seconds
  • Memory Usage 2,204KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (4)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (5)post_thanks_box
  • (5)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete