Thread: Need Pro MYSQL HELP
Results 1 to 2 of 2
01-29-2007, 07:45 PM #1
Need Pro MYSQL HELP
Hi, I'm hoping a mysql pro can help me out here
I've taken over developing a statistics page for a client.
The statistics are recorded in a table named 'visitors'.
This table is about 35,000+ records at the moment.
I need to be able to display total site hits unique hits
and new visitor hits for periods such as today, this week, month and year.
The problem is getting new visitor hits, which would be remote_addr which have not been entered into the database yet. perhaps i need to create a new field called new, and when a unique remote_addr is added for the first time, that record gets a yes in the new field?
It creates a new record on every page thats visited on the website.
The table has 7 fields:
REMOTE_ADDR - logs visitors ip address
HTTP_USER_AGENT - logs browser
HTTP_REFERER - logs referal if exists
SESSION_ID - logs visitors session id
PAGE - logs page visited
DATE_ADDED - date record created - timestamp
Currently the ID fields are used to test if the current remote_addr has a previous record. This is very resource intensive to test each 35,000 rows against each other. Can anyone suggest a better system or a solution?
Thanks in advanced!
I've added an example of the code the previous developer was working on.
The sql below is meant to split up the day by hour showing total hits and new visitors for a specific day.
I've tested the SQL and it times out on the new visitor 'if function'.
':00 - ',
) as `day`,
DATE_FORMAT(date_added, '%W %e %M %Y') AS act_day,
COUNT( DISTINCT REMOTE_ADDR ) AS un_total,
COUNT( DISTINCT session_id) AS site_hits,
v1.REMOTE_ADDR IN (
FROM visitors AS v2
WHERE v2.id < v1.id
GROUP BY REMOTE_ADDR
) ) AS un_new,
FROM visitors AS v1
WHERE DATE(date_added) = DATE('".$_GET['day']."')
AND page != 'REFERAL'
GROUP BY HOUR(date_added)
ORDER BY date_added DESC;
01-30-2007, 03:42 AM #2