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