Snippets

Doug Freed ASM nicks from hosts query

Created by Doug Freed last modified
query.sql
Raw 
1
2
3
4
5
6
7
8
9
select distinct nick from joins as v1
inner join ( 
    select host from joins
    where nick='dwfreed' 
    and host not like '%/session' 
    and host <> '127.0.0.1' 
    ) as v2 
on v1.host = v2.host
where v1.nick not like 'guest%';
snippet.txt
Raw 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
mysql> describe extended select distinct nick from joins as v1 inner join ( select host from joins where nick='dwfreed' and host not like '%/session' and host <> '127.0.0.1' ) as v2 on v1.host = v2.host where v1.nick not like 'guest%';
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref     | rows | filtered | Extra           |
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL    | 1250 |   100.00 | Using temporary |
|  1 | PRIMARY     | v1         | ref  | host          | host | 65      | v2.host |    8 |   100.00 | Using where     |
|  2 | DERIVED     | joins      | ref  | nick,host     | nick | 18      |         |  611 |   100.00 | Using where     |
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+
mysql> explain extended select distinct nick from joins as v1 inner join ( select host from joins where nick='dwfreed' and host not like '%/session' and host <> '127.0.0.1' ) as v2 on v1.host = v2.host where v1.nick not like 'guest%';
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref     | rows | filtered | Extra           |
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL    | 1250 |   100.00 | Using temporary |
|  1 | PRIMARY     | v1         | ref  | host          | host | 65      | v2.host |    8 |   100.00 | Using where     |
|  2 | DERIVED     | joins      | ref  | nick,host     | nick | 18      |         |  611 |   100.00 | Using where     |
+----+-------------+------------+------+---------------+------+---------+---------+------+----------+-----------------+

Comments (0)

HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.