Re: Narrowing a SELECT statement by multiple hits

[shawn l.green]

Hi Jennifer,

On 2/17/2014 3:11 PM, Jennifer wrote:
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`;

You are *so* close!

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
;

ALTER TABLE temp_ip ADD KEY(ip);

SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
INNER JOIN tmp_ip
  ON ip_addresses.ip = tmp_ip.ip
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
ORDER BY INET_ATON(`ip`), `time_stamp`;

Give that a whirl and let us know your results.
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql