mysql> SELECT DISTINCT ht.templateid FROM hosts_templates ht WHERE ht.hostid IN (     SELECT h.hostid     FROM hosts h     WHERE h.flags IN (0,4)         AND h.status IN (0,1)         AND EXISTS (             SELECT NULL             FROM hosts_groups hgg                 JOIN rights r ON r.id=hgg.groupid AND r.groupid=152             WHERE h.hostid=hgg.hostid             GROUP BY hgg.hostid             HAVING MIN(r.permission)>0 AND MAX(r.permission)>=2         ) );
+------------+
| templateid |
+------------+
|      10440 |
|      25519 |
|      30551 |
|      13324 |
|      10504 |
|      10464 |
|      22142 |
|      22143 |
|      25145 |
|      36200 |
|      10490 |
|      29380 |
|      10705 |
|      10496 |
|      10458 |
|      36505 |
|      27209 |
|      12426 |
|      10509 |
|      24377 |
|      27178 |
|      33479 |
+------------+
22 rows in set (2.64 sec)

mysql> EXPLAIN ANALYZE SELECT DISTINCT ht.templateid FROM hosts_templates ht WHERE ht.hostid IN (     SELECT h.hostid     FROM hosts h     WHERE h.flags IN (0,4)         AND h.status IN (0,1)         AND EXISTS (             SELECT NULL             FROM hosts_groups hgg                 JOIN rights r ON r.id=hgg.groupid AND r.groupid=152             WHERE h.hostid=hgg.hostid             GROUP BY hgg.hostid             HAVING MIN(r.permission)>0 AND MAX(r.permission)>=2         ) );

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
| -> Table scan on <temporary>  (cost=4299.74..4345.36 rows=3450) (actual time=2735.621..2735.624 rows=22 loops=1)
    -> Temporary table with deduplication  (cost=4299.73..4299.73 rows=3450) (actual time=2735.619..2735.619 rows=22 loops=1)
        -> Nested loop inner join  (cost=3954.72 rows=3450) (actual time=251.258..2735.198 rows=638 loops=1)
            -> Filter: ((h.flags in (0,4)) and (h.`status` in (0,1)) and exists(select #3))  (cost=2783.20 rows=3060) (actual time=251.242..2733.563 rows=399 loops=1)
                -> Table scan on h  (cost=2783.20 rows=26497) (actual time=0.090..17.028 rows=26285 loops=1)
                -> Select #3 (subquery in condition; dependent)
                    -> Limit: 1 row(s)  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                        -> Filter: ((min(r.permission) > 0) and (max(r.permission) >= 2))  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                            -> Group aggregate: max(r.permission), min(r.permission)  (cost=4.86 rows=0.3) (actual time=0.108..0.108 rows=0 loops=24917)
                                -> Nested loop inner join  (cost=4.84 rows=0.3) (actual time=0.106..0.107 rows=0 loops=24917)
                                    -> Covering index lookup on hgg using hosts_groups_1 (hostid=h.hostid)  (cost=0.82 rows=5) (actual time=0.003..0.005 rows=5 loops=24917)
                                    -> Filter: (r.groupid = 152)  (cost=0.57 rows=0.05) (actual time=0.019..0.019 rows=0 loops=132861)
                                        -> Index lookup on r using rights_2 (id=hgg.groupid)  (cost=0.57 rows=2) (actual time=0.007..0.018 rows=8 loops=132861)
            -> Covering index lookup on ht using hosts_templates_1 (hostid=h.hostid)  (cost=0.27 rows=1) (actual time=0.003..0.004 rows=2 loops=399)
 |
1 row in set, 1 warning (2.74 sec)

mysql> SELECT DISTINCT ht.templateid
    -> FROM hosts_templates ht,hosts h,hosts_groups hg,rights r
    -> WHERE ht.hostid=h.hostid
    ->     AND h.hostid=hg.hostid
    ->     AND hg.groupid=r.id
    ->     AND r.groupid=152
    ->     AND r.permission>=2
    ->     AND h.flags IN (0,4)
    ->     AND h.status IN (0,1)
    ->     AND EXISTS (
    ->         SELECT NULL
    ->         FROM hosts_groups hgg
    ->             JOIN rights r2 ON r2.id=hgg.groupid AND r2.groupid=152
    ->         WHERE h.hostid=hgg.hostid
    ->         GROUP BY hgg.hostid
    ->         HAVING MIN(r2.permission)>0 AND MAX(r2.permission)>=2
    ->     );
+------------+
| templateid |
+------------+
|      13324 |
|      25519 |
|      30551 |
|      36200 |
|      10440 |
|      10458 |
|      36505 |
|      10464 |
|      10490 |
|      29380 |
|      12426 |
|      10705 |
|      10504 |
|      10496 |
|      22142 |
|      22143 |
|      25145 |
|      27209 |
|      10509 |
|      27178 |
|      24377 |
|      33479 |
+------------+
22 rows in set (0.17 sec)

mysql> EXPLAIN ANALYZE SELECT DISTINCT ht.templateid FROM hosts_templates ht,hosts h,hosts_groups hg,rights r WHERE ht.hostid=h.hostid     AND h.hostid=hg.hostid     AND hg.groupid=r.id     AND r.groupid=152     AND r.permission>=2     AND h.flags IN (0,4)     AND h.status IN (0,1)     AND EXISTS (         SELECT NULL         FROM hosts_groups hgg             JOIN rights r2 ON r2.id=hgg.groupid AND r2.groupid=152         WHERE h.hostid=hgg.hostid         GROUP BY hgg.hostid         HAVING MIN(r2.permission)>0 AND MAX(r2.permission)>=2     );

| EXPLAIN |
| -> Table scan on <temporary>  (cost=977.67..981.48 rows=108) (actual time=140.565..140.568 rows=22 loops=1)
    -> Temporary table with deduplication  (cost=977.63..977.63 rows=108) (actual time=140.563..140.563 rows=22 loops=1)
        -> Nested loop inner join  (cost=966.80 rows=108) (actual time=0.563..140.006 rows=1421 loops=1)
            -> Nested loop inner join  (cost=929.99 rows=96) (actual time=0.554..137.096 rows=770 loops=1)
                -> Nested loop inner join  (cost=638.62 rows=832) (actual time=0.205..2.385 rows=770 loops=1)
                    -> Filter: (r.permission >= 2)  (cost=9.92 rows=12) (actual time=0.157..0.172 rows=35 loops=1)
                        -> Index lookup on r using rights_1 (groupid=152)  (cost=9.92 rows=35) (actual time=0.156..0.165 rows=35 loops=1)
                    -> Index lookup on hg using hosts_groups_2 (groupid=r.id)  (cost=47.37 rows=71) (actual time=0.025..0.061 rows=22 loops=35)
                -> Filter: ((h.flags in (0,4)) and (h.`status` in (0,1)) and exists(select #2))  (cost=0.25 rows=0.1) (actual time=0.175..0.175 rows=1 loops=770)
                    -> Single-row index lookup on h using PRIMARY (hostid=hg.hostid)  (cost=0.25 rows=1) (actual time=0.004..0.004 rows=1 loops=770)
                    -> Select #2 (subquery in condition; dependent)
                        -> Limit: 1 row(s)  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                            -> Filter: ((min(r2.permission) > 0) and (max(r2.permission) >= 2))  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                                -> Group aggregate: max(r2.permission), min(r2.permission)  (cost=4.86 rows=0.3) (actual time=0.169..0.169 rows=1 loops=770)
                                    -> Nested loop inner join  (cost=4.84 rows=0.3) (actual time=0.073..0.167 rows=3 loops=770)
                                        -> Covering index lookup on hgg using hosts_groups_1 (hostid=h.hostid)  (cost=0.82 rows=5) (actual time=0.004..0.007 rows=9 loops=770)
                                        -> Filter: (r2.groupid = 152)  (cost=0.57 rows=0.05) (actual time=0.017..0.018 rows=0 loops=6766)
                                            -> Index lookup on r2 using rights_2 (id=hgg.groupid)  (cost=0.57 rows=2) (actual time=0.008..0.018 rows=7 loops=6766)
            -> Covering index lookup on ht using hosts_templates_1 (hostid=hg.hostid)  (cost=0.27 rows=1) (actual time=0.003..0.003 rows=2 loops=770)
 |
1 row in set, 1 warning (0.14 sec)