Re: Update with dependent subquery needs optimization!

[Björn Steinbrink]

Lon B Wrote:
-------------------------------------------------------
> 3. Creating the impression_idx_2 index on
> impression(view_time, network_id) reduced
> execution by 10%

Hm, I'd reverse the order, i.e. (network_id, view_time), the range part should come last.

> Ok, here's some more notes, that hopefully will
> lend more insight. In order to try to use mysql's
> explain tool, I re-wrote the query as a select, it
> runs in almost identical time, so I think I've got
> it right, but haven't figured out how to optimize
> it further:
>
>
> SELECT span.id, count(imp.id)

count(*) should be fine here, imp.id can't be NULL, not sure if MySQL recognizes that correctly itself (and thus avoids reading imp.id)

> FROM interval_span span
> INNER JOIN impression imp ON imp.network_id
> = span.network_id
> INNER JOIN ad_unit au ON imp.ad_unit_id =
> au.id
> WHERE span.begin_date <= '2006-12-03 04:40:00'
> AND span.begin_date >= '2006-12-03 04:00:00'
> AND imp.view_time >= span.begin_date
> AND imp.view_time < span.end_date
> AND au.ad_unit_type_id = span.ad_unit_type_id
> GROUP BY span.id;
>
> +--------+---------------+
> | id | count(imp.id) |
> +--------+---------------+
> | 219136 | 19013 |
> | 219146 | 58026 |
> | 219156 | 35805 |
> +--------+---------------+
> 3 rows in set (2 min 21.20 sec)

Could we get the EXPLAIN output for that one?