slow mysql count because of subselect

how to make this select statement more faster? the first left join with the subselect is making it slower...

mysql>   SELECT COUNT(DISTINCT w1.id) AS AMOUNT FROM tblWerbemittel w1
    JOIN tblVorgang v1 ON w1.object_group = v1.werbemittel_id
    INNER JOIN ( SELECT wmax.object_group, MAX( wmax.object_revision ) wmaxobjrev FROM tblWerbemittel wmax GROUP BY wmax.object_group ) AS wmaxselect ON w1.object_group = wmaxselect.object_group AND w1.object_revision = wmaxselect.wmaxobjrev
    LEFT JOIN ( SELECT vmax.object_group, MAX( vmax.object_revision ) vmaxobjrev FROM tblVorgang vmax GROUP BY vmax.object_group ) AS vmaxselect ON v1.object_group = vmaxselect.object_group AND v1.object_revision = vmaxselect.vmaxobjrev
    LEFT JOIN tblWerbemittel_has_tblAngebot wha ON wha.werbemittel_id = w1.object_group
    LEFT JOIN tblAngebot ta ON ta.id = wha.angebot_id
    LEFT JOIN tblLieferanten tl ON tl.id = ta.lieferant_id AND wha.zuschlag = (SELECT MAX(zuschlag) FROM tblWerbemittel_has_tblAngebot WHERE werbemittel_id = w1.object_group)
    WHERE w1.flags =0 AND v1.flags=0;

        +--------+
        | AMOUNT |
        +--------+
        |   1982 |
        +--------+
        1 row in set (1.30 sec)

Some indexes has been already set and as EXPLAIN shows they were used.

+----+--------------------+-------------------------------+--------+----------------------------------------+----------------------+---------+-----------------------------------------------+------+----------------------------------------------+
| id | select_type        | table                         | type   | possible_keys                          | key                  | key_len | ref                                           | rows | Extra                                        |
+----+--------------------+-------------------------------+--------+----------------------------------------+----------------------+---------+-----------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | <derived2>                    | ALL    | NULL                                   | NULL                 | NULL    | NULL                                          | 2072 |                                              |
|  1 | PRIMARY            | v1                            | ref    | werbemittel_group,werbemittel_id_index | werbemittel_group    | 4       | wmaxselect.object_group                       |    2 | Using where                                  |
|  1 | PRIMARY            | <derived3>                    | ALL    | NULL                                   | NULL                 | NULL    | NULL                                          | 3376 |                                              |
|  1 | PRIMARY            | w1                            | eq_ref | object_revision,or_og_index            | object_revision      | 8       | wmaxselect.wmaxobjrev,wmaxselect.object_group |    1 | Using where                                  |
|  1 | PRIMARY            | wha                           | ref    | PRIMARY,werbemittel_id_index           | werbemittel_id_index | 4       | dpd.w1.object_group                           |    1 |                                              |
|  1 | PRIMARY            | ta                            | eq_ref | PRIMARY                                | PRIMARY              | 4       | dpd.wha.angebot_id                            |    1 |                                              |
|  1 | PRIMARY            | tl                            | eq_ref | PRIMARY                                | PRIMARY              | 4       | dpd.ta.lieferant_id                           |    1 | Using index                                  |
|  4 | DEPENDENT SUBQUERY | tblWerbemittel_has_tblAngebot | ref    | PRIMARY,werbemittel_id_index          开发者_如何学Python | werbemittel_id_index | 4       | dpd.w1.object_group                           |    1 |                                              |
|  3 | DERIVED            | vmax                          | index  | NULL                                   | object_revision_uq   | 8       | NULL                                          | 4668 | Using index; Using temporary; Using filesort |
|  2 | DERIVED            | wmax                          | range  | NULL                                   | or_og_index          | 4       | NULL                                          | 2168 | Using index for group-by                     |
+----+--------------------+-------------------------------+--------+----------------------------------------+----------------------+---------+-----------------------------------------------+------+----------------------------------------------+
10 rows in set (0.01 sec)

The main problem while the statement above takes about 2 seconds seems to be the subselect where no index can be used. How to write the statement even more faster?

Thanks for help. MT

Do you have the following indexes?

for tblWerbemittel - object_group, object_revision
for tblVorgang - object_group, object_revision
for tblWerbemittel_has_tblAngebot - werbemittel_id, zuschlag

Let me know if that helps, there are a few more that I can see might help but try those first.

EDIT

Can you try these two queries and see if they run fast?

SELECT  w1.id AS AMOUNT 
FROM    tblWerbemittel w1 INNER JOIN 
        (SELECT     wmax.object_group, 
                    MAX( wmax.object_revision ) AS wmaxobjrev 
        FROM        tblWerbemittel AS wmax 
        GROUP BY wmax.object_group ) AS wmaxselect ON   w1.object_group = wmaxselect.object_group AND 
                                                        w1.object_revision = wmaxselect.wmaxobjrev      
WHERE   w1.flags = 0 

SELECT  v1.werbemittel_id 
FROM    tblVorgang v1 LEFT JOIN
        (SELECT     vmax.object_group, 
                    MAX( vmax.object_revision ) AS vmaxobjrev 
        FROM        tblVorgang AS vmax 
        GROUP BY    vmax.object_group ) AS  vmaxselect ON v1.object_group = vmaxselect.object_group AND 
                                            v1.object_revision = vmaxselect.vmaxobjrev LEFT JOIN 
WHERE   v1.flags = 0

While I consider I don't have sufficient data to provide a 100% correct answer, but I can throw in a handful of tips.

Forst of all, MYSQL is stupid. Bear that in mind and always rearrange your queries so that the most data is excluded at the beginning. For instance, if the last join reduced the number of results from 10k to 2k while the others don't, try swapping their positions so that each subsequent join operates on the smallest subset of data possible.

Same applies to the WHERE clause.

Also, joins tend to be slower than subqueries. I don't know if that's a rule or just something that I'm observing in my case, but you can always try to substitute a join or two with a subquery.

While I suppose this doesn't really answer your question, I hope it at least gives you an idea about where to start looking for optimisations.