List:General Discussion« Previous MessageNext Message »
From:Roy Lyseng Date:February 17 2014 8:36pm
Subject:Re: Narrowing a SELECT statement by multiple hits
View as plain text  
Hi Jennifer,

great that it worked.

Try replacing the line

   `ip` IN (temp_ip)

with

   `ip` IN (SELECT ip FROM temp_ip)

Each subquery needs to be a complete SELECT query.

Thanks,
Roy

On 17.02.14 21:11, Jennifer wrote:
> 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