Reputation: 45
Say I have a table structure below. I am looking to find only those IPs that have made just the HEAD request. In this case IP6.
mysql> select distinct ip, method from request_records;
+-------+---------+
| ip | method |
+-------+---------+
| IP1 | GET |
| IP1 | POST |
| IP1 | OPTIONS |
| IP1 | HEAD |
| IP2 | GET |
| IP2 | POST |
| IP2 | OPTIONS |
| IP2 | HEAD |
| IP3 | GET |
| IP4 | POST |
| IP5 | OPTIONS |
| IP6 | HEAD |
+-------+---------+
Using the NOT IN condition returns other IPs which is incorrect (seen below). How do I check across multiple rows
mysql> select distinct ip,method from request_records where method not in ('GET','POST','OPTIONS');
+-------+-------+
| ip | method |
+-------+-------+
| IP1 | HEAD |
| IP2 | HEAD |
| IP6 | HEAD |
+-------+-------+
Upvotes: 0
Views: 51
Reputation: 1606
I would not use aggregation for this situation (they are too inefficient in this case and as I expect it might be quete a lot of records in the table).
So, I'd use something like this:
-- explain
SELECT
t1.ip
FROM
request_records AS t1
LEFT JOIN request_records AS t2 ON (
t2.ip = t1.ip
AND t2.method != t1.method
)
WHERE
t1.method = 'HEAD'
AND t2.ip IS NULL
indexes (ip, method) and (method, ip) are recommended.
Explain results:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ref IX_method_ip IX_method_ip 17 const 3 100.00 Using index
1 SIMPLE t2 NULL ref IX_method_ip,IX_ip_method IX_ip_method 17 t1.ip 2 100.00 Using where; Not exists; Using index
Upvotes: 0
Reputation: 1269683
You can use aggregation:
select ip
from request_records
group by ip
having min(request) = max(request) and min(request) = 'HEAD';
If you have a separate table of unique ip
s, then I would recommend not exists
:
select ip
from ips
where not exists (select 1
from request_record rr
where rr.ip = ips.ip and
rr.request <> 'HEAD'
);
Of course, this will return rows with no 'HEAD'
-- which may or may not be what you want. You can add:
select ip
from ips
where not exists (select 1
from request_record rr
where rr.ip = ips.ip and
rr.request <> 'HEAD'
) and
exists (select 1
from request_record rr
where rr.ip = ips.ip and
rr.request = 'HEAD'
);
Upvotes: 1