mariadb_5.5.32_result

result:

+----------+----+
| filename | id |
+----------+----+
| NULL     |  1 |
| NULL     |  2 |
| NULL     |  3 |
| NULL     |  4 |
| NULL     | 12 |
| NULL     | 14 |
| NULL     | 16 |
| NULL     |  5 |
| NULL     |  9 |
| NULL     |  7 |
| NULL     |  6 |
| NULL     |  8 |
| NULL     | 10 |
| NULL     | 11 |
| NULL     | 13 |
| NULL     | 15 |
+----------+----+

extended explain:

+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
| id   | select_type | table | type   | possible_keys                                                             | key                     | key_len | ref                        | rows | filtered | Extra       |
+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|    1 | SIMPLE      | t6    | index  | NULL                                                                      | fk_gt_tbl_contact_8_idx | 5       | NULL                       |   16 |   100.00 | Using index |
|    1 | SIMPLE      | c     | ref    | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved    | 2       | const                      |    2 |   100.00 | Using where |
|    1 | SIMPLE      | d     | ref    | Index_2,Index_3                                                           | Index_2                 | 105     | thingtest2.c.ContentHistID |    6 |   100.00 | Using where |
|    1 | SIMPLE      | a     | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 4       | thingtest2.d.attributeID   |    1 |   100.00 | Using where |
|    1 | SIMPLE      | es    | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 105     | thingtest2.a.extendSetID   |    1 |   100.00 | Using where |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY                                                                   | PRIMARY                 | 105     | thingtest2.es.subTypeID    |    1 |   100.00 | Using where |
+------+-------------+-------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+

mysql_5.1.69_result

result:

+-----------+----+
| filename  | id |
+-----------+----+
| NULL      |  1 |
| NULL      |  2 |
| NULL      |  3 |
| NULL      |  4 |
| NULL      | 12 |
| NULL      | 14 |
| NULL      | 16 |
| 17944.pdf |  5 |
| 17942.pdf |  9 |
| 17941.pdf |  7 |
| 17940.pdf |  6 |
| 17965.pdf |  8 |
| 17965.pdf | 10 |
| 17965.pdf | 11 |
| 17965.pdf | 13 |
| 17965.pdf | 15 |
+-----------+----+

extended explain:

+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
| id | select_type | table      | type   | possible_keys                                                             | key                     | key_len | ref                        | rows | filtered | Extra       |
+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+
|  1 | PRIMARY     | t6         | index  | NULL                                                                      | fk_gt_tbl_contact_8_idx | 5       | NULL                       |   16 |   100.00 | Using index |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                      | NULL                    | NULL    | NULL                       |   22 |   100.00 |             |
|  2 | DERIVED     | c          | ref    | IX_TContent_1,IX_tcontent_approved,IX_tcontent_active,IX_tcontent_display | IX_tcontent_approved    | 2       |                            |   11 |   100.00 | Using where |
|  2 | DERIVED     | d          | ref    | Index_2,Index_3                                                           | Index_2                 | 105     | thingtest2.c.ContentHistID |    3 |   100.00 |             |
|  2 | DERIVED     | a          | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 4       | thingtest2.d.attributeID   |    1 |   100.00 |             |
|  2 | DERIVED     | es         | eq_ref | PRIMARY,Index_2                                                           | PRIMARY                 | 105     | thingtest2.a.extendSetID   |    1 |   100.00 |             |
|  2 | DERIVED     | e          | eq_ref | PRIMARY                                                                   | PRIMARY                 | 105     | thingtest2.es.subTypeID    |    1 |   100.00 | Using where |
+----+-------------+------------+--------+---------------------------------------------------------------------------+-------------------------+---------+----------------------------+------+----------+-------------+

query.sql

SELECT v.filename, t6.id
FROM gt_tbl_contact t6 
LEFT JOIN (
SELECT d.attributeValue as grantee_id
                , c.filename as filename,
    d.baseId
FROM tclassextend e
JOIN tclassextendsets es on es.subTypeID = e.subTypeID
JOIN tclassextendattributes a on a.extendSetID = es.extendSetID
JOIN tclassextenddata d on d.attributeID = a.attributeID
JOIN tcontent c on c.contentHistID = d.baseID
WHERE 
c.active = 1 
                AND c.approved = 1 
                AND c.display = 1
                AND e.subType = 'Grantee') v ON v.grantee_id = t6.contact_orgid;