db_rewrite_sql performance

The query created by og_forum_db_rewrite_sql() is running VERY long on my particular system, almost 40 seconds.... here's an analysis:

mysql> DESC SELECT  DISTINCT(t.tid), t.*, parent FROM term_data t INNER JOIN  term_hierarchy h ON t.tid = h.tid   WHERE (t.tid NOT IN (SELECT x.tid FROM (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 5) x      WHERE x.tid NOT IN (SELECT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 5))       AND t.tid NOT IN (SELECT ogt.tid FROM og_term ogt IINNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 5 WHERE ogu.is_active = 0)) AND ( t.vid = 2 ) ORDER BY weight, name;I
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------------------------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref                        | rows   | Extra                                        |
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------------------------------------------+
|  1 | PRIMARY            | t          | ref    | PRIMARY,vid   | vid     | 4       | const                      |    130 | Using where; Using temporary; Using filesort | 
|  1 | PRIMARY            | h          | ref    | PRIMARY,tid   | PRIMARY | 4       | unity08_dev2.t.tid         |      7 | Using index                                  | 
|  5 | DEPENDENT SUBQUERY | ogt        | ref    | PRIMARY       | PRIMARY | 4       | func                       |      1 | Using where; Using index                     | 
|  5 | DEPENDENT SUBQUERY | ogu        | eq_ref | PRIMARY       | PRIMARY | 8       | unity08_dev2.ogt.nid,const |      1 | Using where                                  | 
|  2 | DEPENDENT SUBQUERY | <derived3> | ALL    | NULL          | NULL    | NULL    | NULL                       | 144540 | Using where                                  | 
|  4 | DEPENDENT SUBQUERY | ogt        | ref    | PRIMARY       | PRIMARY | 4       | func                       |      1 | Using where; Using index                     | 
|  4 | DEPENDENT SUBQUERY | ogu        | eq_ref | PRIMARY       | PRIMARY | 8       | unity08_dev2.ogt.nid,const |      1 | Using where; Using index                     | 
|  3 | DERIVED            | ogt        | index  | NULL          | PRIMARY | 8       | NULL                       |    106 | Using index                                  | 
|  3 | DERIVED            | ogu        | ref    | PRIMARY       | PRIMARY | 4       | unity08_dev2.ogt.nid       |    722 | Using where; Using index                     | 
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+--------+----------------------------------------------+

Insertint DISTINCT keywords into all the subqueries that are used in NOT IN clauses substantially improves performance, in my case down to 0.21 seconds. Here you can see why:


mysql> DESC SELECT DISTINCT(t.tid), t.*, parent FROM term_data t INNER JOIN term_hierarchy h ON t.tid = h.tid WHERE (t.tid NOT IN (SELECT DISTINCT x.tid FROM (SELECT DISTINCT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid != 5) x WHERE x.tid NOT IN (SELECT DISTINCT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 5)) AND t.tid NOT IN (SELECT DISTINCT ogt.tid FROM og_term ogt INNER JOIN og_uid ogu ON ogt.nid = ogu.nid AND ogu.uid = 5 WHERE ogu.is_active = 0)) AND ( t.vid = 2 ) ORDER BY weight, name;
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+------+----------------------------------------------+
| 1 | PRIMARY | t | ref | PRIMARY,vid | vid | 4 | const | 130 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | h | ref | PRIMARY,tid | PRIMARY | 4 | unity08_dev2.t.tid | 7 | Using index |
| 5 | DEPENDENT SUBQUERY | ogt | ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary |
| 5 | DEPENDENT SUBQUERY | ogu | eq_ref | PRIMARY | PRIMARY | 8 | unity08_dev2.ogt.nid,const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | | ALL | NULL | NULL | NULL | NULL | 106 | Using where; Using temporary |
| 4 | DEPENDENT SUBQUERY | ogt | ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary |
| 4 | DEPENDENT SUBQUERY | ogu | eq_ref | PRIMARY | PRIMARY | 8 | unity08_dev2.ogt.nid,const | 1 | Using where; Using index |
| 3 | DERIVED | ogt | index | NULL | PRIMARY | 8 | NULL | 106 | Using index; Using temporary |
| 3 | DERIVED | ogu | ref | PRIMARY | PRIMARY | 4 | unity08_dev2.ogt.nid | 722 | Using where; Using index |
+----+--------------------+------------+--------+---------------+---------+---------+----------------------------+------+----------------------------------------------+

Note the difference in line 5... scanning 106 rows instead of 144540. Patch attached.