explain query

output

+----+-------------+------------+--------+--------------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+--------------------------+
| id | select_type | table      | type   | possible_keys                                                            | key                   | key_len | ref                          | rows  | Extra                    |
+----+-------------+------------+--------+--------------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+--------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                                     | NULL                  | NULL    | NULL                         | 28189 | Using where              |
|  1 | PRIMARY     | er         | eq_ref | PRIMARY                                                                  | PRIMARY               | 4       | f0_max.entity_record_id      |     1 |                          |
|  2 | DERIVED     | <derived3> | ALL    | NULL                                                                     | NULL                  | NULL    | NULL                         | 28189 |                          |
|  2 | DERIVED     | <derived4> | ALL    | NULL                                                                     | NULL                  | NULL    | NULL                         | 28189 | Using where; Not exists  |
|  4 | DERIVED     | e          | range  | PRIMARY,name_index                                                       | name_index            | 767     | NULL                         |     4 | Using where; Using index |
|  4 | DERIVED     | p          | ref    | PRIMARY,IDX_DBE64F3781257D5D,entity_id_name_index                        | entity_id_name_index  | 772     | cdpaccess.e.id               |     1 | Using where; Using index |
|  4 | DERIVED     | pt         | ref    | PRIMARY,property_id_type_idx,IDX_CE2EBB09549213EC,property_id_type_index | property_id_type_idx  | 772     | cdpaccess.p.id               |     1 | Using where; Using index |
|  4 | DERIVED     | cr         | ref    | PRIMARY,IDX_E3DFE149C81C6EB,IDX_E3DFE144BBC2705                          | IDX_E3DFE149C81C6EB   | 5       | cdpaccess.pt.id              |  4028 | Using where              |
|  4 | DERIVED     | v          | eq_ref | PRIMARY,IDX_AE3E06B382FD956A,IDX_AE3E06B392AE854F                        | PRIMARY               | 4       | cdpaccess.cr.version_id      |     1 | Using where              |
|  4 | DERIVED     | ut         | eq_ref | PRIMARY                                                                  | PRIMARY               | 4       | cdpaccess.v.update_type_id   |     1 |                          |
|  4 | DERIVED     | vg         | eq_ref | PRIMARY                                                                  | PRIMARY               | 4       | cdpaccess.v.version_group_id |     1 |                          |
|  4 | DERIVED     | tc         | ref    | UNIQ_5A2C076C7D1A3D0F                                                    | UNIQ_5A2C076C7D1A3D0F | 5       | cdpaccess.cr.id              |     1 | Using where              |
|  3 | DERIVED     | e          | range  | PRIMARY,name_index                                                       | name_index            | 767     | NULL                         |     4 | Using where; Using index |
|  3 | DERIVED     | p          | ref    | PRIMARY,IDX_DBE64F3781257D5D,entity_id_name_index                        | entity_id_name_index  | 772     | cdpaccess.e.id               |     1 | Using where; Using index |
|  3 | DERIVED     | pt         | ref    | PRIMARY,property_id_type_idx,IDX_CE2EBB09549213EC,property_id_type_index | property_id_type_idx  | 772     | cdpaccess.p.id               |     1 | Using where; Using index |
|  3 | DERIVED     | cr         | ref    | PRIMARY,IDX_E3DFE149C81C6EB,IDX_E3DFE144BBC2705                          | IDX_E3DFE149C81C6EB   | 5       | cdpaccess.pt.id              |  4028 | Using where              |
|  3 | DERIVED     | v          | eq_ref | PRIMARY,IDX_AE3E06B382FD956A,IDX_AE3E06B392AE854F                        | PRIMARY               | 4       | cdpaccess.cr.version_id      |     1 | Using where              |
|  3 | DERIVED     | ut         | eq_ref | PRIMARY                                                                  | PRIMARY               | 4       | cdpaccess.v.update_type_id   |     1 |                          |
|  3 | DERIVED     | vg         | eq_ref | PRIMARY                                                                  | PRIMARY               | 4       | cdpaccess.v.version_group_id |     1 |                          |
|  3 | DERIVED     | tc         | ref    | UNIQ_5A2C076C7D1A3D0F                                                    | UNIQ_5A2C076C7D1A3D0F | 5       | cdpaccess.cr.id              |     1 | Using where              |
+----+-------------+------------+--------+--------------------------------------------------------------------------+-----------------------+---------+------------------------------+-------+--------------------------+
20 rows in set (11 min 11.74 sec)

queryexplain

EXPLAIN 
SELECT
  er.loggedEntityId AS id 
FROM
  audit_entity_record er 
  INNER JOIN
    (
      SELECT
        o.entity_record_id,
        o.content 
      FROM
        (
          SELECT
            ut.entity_record_id,
            cr.id,
            vg.TIMESTAMP,
            tc.content 
          FROM
            audit_content_record cr 
            INNER JOIN
              audit_entity e 
              ON e.name IN 
              (
                'ICC\\CdpAccessBundle\\Entity\\ProposalObjects\\ProposalCodeObject',
                'ICC\\CdpAccessBundle\\Entity\\ProposalObjects\\AbstractProposalObject',
                'ICC\\ProposalBundle\\Entity\\ProposalObjects\\ProposalCodeObject',
                'ICC\\ProposalBundle\\Entity\\ProposalObjects\\AbstractProposalObject'
              )
            INNER JOIN
              audit_property p 
              ON p.name = 'proposalObjectEntity' 
              AND p.entity_id = e.id 
            INNER JOIN
              audit_property_type pt 
              ON pt.type = 'TEXT' 
              AND pt.property_id = p.id 
              AND pt.id = cr.property_type_id 
            INNER JOIN
              audit_version v 
              ON v.id = cr.version_id 
            INNER JOIN
              audit_version_group vg 
              ON vg.id <= '935559' 
              AND vg.id = v.version_group_id 
            INNER JOIN
              audit_update_type ut 
              ON ut.id = v.update_type_id 
            INNER JOIN
              audit_text_content tc 
              ON tc.content_record_id = cr.id
        )
        o 
        LEFT JOIN
          (
            SELECT
              ut.entity_record_id,
              cr.id,
              vg.TIMESTAMP,
              tc.content 
            FROM
              audit_content_record cr 
              INNER JOIN
                audit_entity e 
                ON e.name IN 
                (
                  'ICC\\CdpAccessBundle\\Entity\\ProposalObjects\\ProposalCodeObject',
                  'ICC\\CdpAccessBundle\\Entity\\ProposalObjects\\AbstractProposalObject',
                  'ICC\\ProposalBundle\\Entity\\ProposalObjects\\ProposalCodeObject',
                  'ICC\\ProposalBundle\\Entity\\ProposalObjects\\AbstractProposalObject'
                )
              INNER JOIN
                audit_property p 
                ON p.name = 'proposalObjectEntity' 
                AND p.entity_id = e.id 
              INNER JOIN
                audit_property_type pt 
                ON pt.type = 'TEXT' 
                AND pt.property_id = p.id 
                AND pt.id = cr.property_type_id 
              INNER JOIN
                audit_version v 
                ON v.id = cr.version_id 
              INNER JOIN
                audit_version_group vg 
                ON vg.id <= '935559' 
                AND vg.id = v.version_group_id 
              INNER JOIN
                audit_update_type ut 
                ON ut.id = v.update_type_id 
              INNER JOIN
                audit_text_content tc 
                ON tc.content_record_id = cr.id
          )
          b 
          ON b.entity_record_id = o.entity_record_id 
          AND o.TIMESTAMP > b.TIMESTAMP 
      WHERE
        b.TIMESTAMP IS NULL 
    )
    f0_max 
    ON f0_max.entity_record_id = er.id 
    AND f0_max.content = '25545'