Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread Jennifer 
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 COUNT(ip) 1
 ORDER BY INET_ATON(`ip`), `time_stamp`

Thank you Larry for the response.  Unfortunately, I can't get it to 
work.  The code above only returns one row.  It should return 35 rows.  If I 
remove the HAVING COUNT line, 52 rows are returned.

If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that 
there are 52 records, but still only returns one row.  So I added GROUP BY `ip` 
and that gave me 7 rows with counts that added up to 35.  Closer, but each row 
was a group of IP addresses where there was more than one hit.  

I want each hit to be returned, not a summary of hits per IP, so I 
don't think GROUP BY is what I need(?).  I've run across a couple of sites that 
seem to say that an INNER JOIN would give me what I want.  If that's true, then 
that's above my head.

BTW, this on MySQL 5.5.34-cll-lve

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Wire LAN Shelving:
http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php


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

Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread Roy Lyseng 

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 existing where clause AND
  ip IN (SELECT ip
 FROM ip_addresses
 WHERE existing where clause
 GROUP BY ip
 HAVING COUNT(*)  1
)

I think this will be more performant on version 5.6, though. If this is too 
slow, you may try materializing the subquery in a temporary table and use that 
table instead of the subquery.


Thanks,
Roy

On 17.02.14 17:42, Jennifer wrote:

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 COUNT(ip) 1
ORDER BY INET_ATON(`ip`), `time_stamp`


Thank you Larry for the response.  Unfortunately, I can't get it to 
work.  The code above only returns one row.  It should return 35 rows.  If I 
remove the HAVING COUNT line, 52 rows are returned.

If I add COUNT(`ip`) AS UniqueIPs to the SELECT line, that shows that 
there are 52 records, but still only returns one row.  So I added GROUP BY `ip` 
and that gave me 7 rows with counts that added up to 35.  Closer, but each row 
was a group of IP addresses where there was more than one hit.

I want each hit to be returned, not a summary of hits per IP, so I 
don't think GROUP BY is what I need(?).  I've run across a couple of sites that seem to 
say that an INNER JOIN would give me what I want.  If that's true, then that's above my 
head.

BTW, this on MySQL 5.5.34-cll-lve

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Wire LAN Shelving:
http://www.superiorshelving.com/mfg/nexel/pages/wire-shelving-chrome.php





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

Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread Jennifer 
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

Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread Roy Lyseng 

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




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

Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread 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

Re: Narrowing a SELECT statement by multiple hits

2014-02-17 Thread Jennifer 
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 
complete SELECT statement from before, that was good enough, but no.  There's 
always something new to learn!

Thanks again,
Jenni

Superior Shelving Systems
The (Storage|Office|Display|Warehouse) Shelving Specialists
Since 1984

Metro Shelving:
http://www.superiorshelving.com/mfg/metro/


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

Narrowing a SELECT statement by multiple hits

2014-02-12 Thread Jennifer 
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.  

How can I add a condition to only show hits by someone who's hit the 
site 2 or more times with the same IP?  I tried GROUP BY but that didn't return 
all the hits - one one per IP.

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 '' 
ORDER BY INET_ATON(`ip`), `time_stamp`

I hope I'm explaining this correctly.

Thank you,
Jenni

Superior Shelving Systems
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984

Computer Workstations:
http://www.superiorshelving.com/mfg/nexel/pages/lan-workstations-nexel.php


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

Re: Narrowing a SELECT statement by multiple hits

2014-02-12 Thread Larry Martell 
On Wed, Feb 12, 2014 at 7:35 PM, Jennifer jenni...@superiorshelving.com 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 
 from the same IP address - not just a single hit by someone.

 How can I add a condition to only show hits by someone who's hit the 
 site 2 or more times with the same IP?  I tried GROUP BY but that didn't 
 return all the hits - one one per IP.

 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 ''
 ORDER BY INET_ATON(`ip`), `time_stamp`

 I hope I'm explaining this correctly.

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 COUNT(ip) 2
ORDER BY INET_ATON(`ip`), `time_stamp`

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