List:General Discussion« Previous MessageNext Message »
From:Jennifer Date:February 17 2014 8:11pm
Subject:Re: Narrowing a SELECT statement by multiple hits
View as plain text  
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

Thread
Narrowing a SELECT statement by multiple hitsJennifer13 Feb 2014
  • Re: Narrowing a SELECT statement by multiple hitsLarry Martell13 Feb 2014
    • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
      • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
        • Re: Narrowing a SELECT statement by multiple hitsJennifer17 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsRoy Lyseng17 Feb 2014
            • Re: Narrowing a SELECT statement by multiple hitsJennifer18 Feb 2014
          • Re: Narrowing a SELECT statement by multiple hitsshawn l.green17 Feb 2014