On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:
> please try filtering with a subquery that locates ip addresses with more than
> 1 attempt:
Hi Roy,
That did it! Thank you so much!!! Now I just need to study that IN
clause to see what's going on there.
> If this is too slow, you may try materializing the subquery in a temporary
> table and use that table instead of the subquery.
Yes, it is slow. It takes about 15 seconds to finish, but since it's
run once a day via cron to email me a report, I don't think it's too much of a
problem. However, I did try to see if I could do it, but there's an error
somewhere in the SQL. What am I doing wrong?
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HAVING COUNT(*) > 1
);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' AND
`ip` IN (temp_ip)
ORDER BY INET_ATON(`ip`), `time_stamp`;
Thank you again,
Jenni
Superior Shelving Systems::::....
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984
Workstation Chairs/Stools:
http://www.superiorshelving.com/mfg/nexel/pages/stools.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
