Page 2 of 3
Posted: Fri Jun 03, 2005 7:04 pm
by Todd_Z
I thought it would be to add COUNT() in there: SELECT COUNT(v1.IP), however, this simply returns the number of times that the ip address is in the database.
Posted: Fri Jun 03, 2005 7:09 pm
by Burrito
try count(distinct())
Posted: Fri Jun 03, 2005 7:28 pm
by Todd_Z
Changes each value to 1, but still returns the amount of rows as IPs fit the criteria.
Posted: Fri Jun 03, 2005 7:53 pm
by Burrito
Todd_Z wrote:Changes each value to 1, but still returns the amount of rows as IPs fit the criteria.
does that mean it worked?
Posted: Fri Jun 03, 2005 7:58 pm
by Todd_Z
What I mean is it returns the following:
1
1
1
1
instead of 4
Posted: Fri Jun 03, 2005 8:42 pm
by Burrito
Code: Select all
$sql = "SELECT count(distinct(v1.IP)) as thismany FROM visitors v1 INNER JOIN visitors v2 ON v2.IP = v1.IP WHERE (v1.timestamp BETWEEN lower_limit AND upper_limit) AND v2.timestamp < lower_limit GROUP BY v1.IP";
// then echo thismany...
what does that do for you?
Posted: Fri Jun 03, 2005 8:45 pm
by Todd_Z
Code: Select all
$sql = 'SELECT COUNT(DISTINCT(v1.IP)) as thismany FROM Visitors v1 INNER JOIN Visitors v2 ON v2.IP = v1.IP WHERE (v1.timestamp BETWEEN 1117800000 AND 1117803599) AND v2.timestamp < 1117800000 GROUP BY v1.IP LIMIT 0, 30';
Yields:
thismany
1
1
1
1
1
Posted: Fri Jun 03, 2005 9:48 pm
by Burrito
that's really odd, it should only return one row. What does mysql_num_rows tell you?
Posted: Fri Jun 03, 2005 10:29 pm
by Todd_Z
in that case, 5.
Posted: Fri Jun 03, 2005 10:34 pm
by Burrito
unfortunately I don't know then...
it could be the joins doing something that I don't know about.
perhaps one of the other's who's posted in this thread can shed some light as I'm fresh out of ideas.
sorry mang
Posted: Sat Jun 04, 2005 3:46 am
by timvw
Everah wrote:
Maybe add one more step to it and set v1.IP equal to v2.IP...
That is already done by the INNER JOIN

Posted: Sat Jun 04, 2005 3:49 am
by timvw
@Todd_Z
please notice my query didn't have a GROUP BY (Well it's shouldn't have)
Code: Select all
SELECT COUNT(DISTINCT V1.IP) AS count
FROM visitors AS v1
INNER JOIN visitors AS v2
WHERE v1.timestamp < $start
AND v2.timestamp BETWEEN $start AND $end
Posted: Sat Jun 04, 2005 11:07 am
by Todd_Z
Timvw, that works perfectly, however, it takes sooo long to do. The load time for the page is unacceptably long. Is there anyway to do this much faster? Would it be better if i just caved and did it in php?
Posted: Sat Jun 04, 2005 11:58 am
by timvw
lookup what EXPLAIN can do for you...
lookup on which columns an INDEX can help...
do your own benchmarking mysql<->php processing...
Posted: Sat Jun 04, 2005 12:06 pm
by Todd_Z
Structure Dump:
CREATE TABLE `Visitors` (
`IP` text NOT NULL,
`Timestamp` int(10) unsigned NOT NULL default '0',
`Page` text NOT NULL,
`Referer` text NOT NULL,
`Browser` text NOT NULL,
`Platform` text NOT NULL,
`ID` int(20) unsigned NOT NULL auto_increment,
UNIQUE KEY `ID` (`ID`)
) TYPE=MyISAM;
results of that sql with EXPLAIN at the beginning:
table type possible_keys key key_len ref rows Extra
v1 ALL NULL NULL NULL NULL 6048 Using where
v2 ALL NULL NULL NULL NULL 6048 Using where