On Feb 17, 2014, at 12:36 PM, Roy Lyseng wrote:
> Try... `ip` IN (SELECT ip FROM temp_ip)
Wow! Only 1 second to return the results now!! That's 15x faster!!!
> Each subquery needs to be a complete SELECT query.
That's good to know. I figured that since temp_ip referenced a
co
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(
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 wit
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
Hi Jennifer,
please try filtering with a subquery that locates ip addresses with more than 1
attempt:
SELECT ip, page, url, time_stamp
FROM ip_adresses
WHERE AND
ip IN (SELECT ip
FROM ip_addresses
WHERE
GROUP BY ip
HAVING COUNT(*) > 1
On Feb 12, 2014, at 6:30 PM, Larry Martell wrote:
> Try adding a having clause, e.g.:
>
> 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 ''
> HAVING C
On Wed, Feb 12, 2014 at 7:35 PM, Jennifer wrote:
> Hello,
>
> I have the following SQL statement that I'd like to add to. It's
> used to create a report that's emailed to me showing hits to our site that
> didn't provide a referrer. However, I only want to report on multiple hits
> fr
Hello,
I have the following SQL statement that I'd like to add to. It's used
to create a report that's emailed to me showing hits to our site that didn't
provide a referrer. However, I only want to report on multiple hits from the
same IP address - not just a single hit by someone.
