Derived table performance

Hi,

just out of curiosity I'd like to know why

SELECT * FROM history WHERE history_id IN (SELECT col FROM (SELECT MAX
(history_id) AS col FROM history JOIN (...) AS intermediate USING
(order_id) GROUP BY order_id) AS trick)

which takes about a minute is so much but yet so little slower than

SELECT col FROM (SELECT MAX(history_id) AS col FROM history JOIN (...)
AS intermediate USING (order_id) GROUP BY order_id) AS trick

which is basically the same but with the outmost query removed and
finishes in about 6 seconds.

Here is the execution plan of the former:

+----+--------------------+-------------+--------
+-------------------------+----------+---------+----------+---------
+--------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len | ref | rows |
Extra |
+----+--------------------+-------------+--------
+-------------------------+----------+---------+----------+---------
+--------------------------------+
| 1 | PRIMARY | history | ALL |
NULL | NULL | NULL | NULL | 1394471 |
Using where |
| 2 | DEPENDENT SUBQUERY | <derived3> | system |
NULL | NULL | NULL | NULL | 0 |
const row not found |
| 3 | DERIVED | NULL | NULL |
NULL | NULL | NULL | NULL | NULL | no
matching row in const table |
| 4 | DERIVED | orders | range |
PRIMARY | PRIMARY | 4 | NULL | 20308 |
Using where; Using index |
| 7 | DEPENDENT SUBQUERY | inner_table | ALL |
order_id | order_id | 4 | order_id | 20 |
Using filesort |
| 6 | DEPENDENT SUBQUERY | inner_table | ALL |
order_id | order_id | 4 | order_id | 20 |
Using filesort |
| 5 | DEPENDENT SUBQUERY | inner_table | ref |
order_id,event_type,o_e | o_e | 261 | order_id | 1 |
Using where; Using index |
+----+--------------------+-------------+--------
+-------------------------+----------+---------+----------+---------
+--------------------------------+

When I did this EXPLAIN there were no rows in the derived table. When
I did the time measurements it were about 200 rows.

Interestingly it took the server 20 seconds to do the EXPLAIN.

I don't understand the factor 10 in the time difference. It is
certainly not the number of rows in table "history" as it would be if
the subquery would be executed for each line in history.


From older experiences I have always been sceptical whether MySQL
caches a derived table or not. I did experiments with many small
subtables compared to one large subtable and found that often the many
small subtables are much faster. Is this a matter of available memory?

Regards,
André

André Hänsel wrote:
> Hi,
>
> just out of curiosity I'd like to know why
>
> SELECT * FROM history WHERE history_id IN (SELECT col FROM (SELECT MAX
> (history_id) AS col FROM history JOIN (...) AS intermediate USING
> (order_id) GROUP BY order_id) AS trick)
>
> which takes about a minute is so much but yet so little slower than
>
> SELECT col FROM (SELECT MAX(history_id) AS col FROM history JOIN (...)
> AS intermediate USING (order_id) GROUP BY order_id) AS trick
>
> which is basically the same but with the outmost query removed and
> finishes in about 6 seconds.
>
> Here is the execution plan of the former:
>
> +----+--------------------+-------------+--------
> +-------------------------+----------+---------+----------+---------
> +--------------------------------+
> | id | select_type | table | type |
> possible_keys | key | key_len | ref | rows |
> Extra |
> +----+--------------------+-------------+--------
> +-------------------------+----------+---------+----------+---------
> +--------------------------------+
> | 1 | PRIMARY | history | ALL |
> NULL | NULL | NULL | NULL | 1394471 |
> Using where |
> | 2 | DEPENDENT SUBQUERY | <derived3> | system |
> NULL | NULL | NULL | NULL | 0 |
> const row not found |
> | 3 | DERIVED | NULL | NULL |
> NULL | NULL | NULL | NULL | NULL | no
> matching row in const table |
> | 4 | DERIVED | orders | range |
> PRIMARY | PRIMARY | 4 | NULL | 20308 |
> Using where; Using index |
> | 7 | DEPENDENT SUBQUERY | inner_table | ALL |
> order_id | order_id | 4 | order_id | 20 |
> Using filesort |
> | 6 | DEPENDENT SUBQUERY | inner_table | ALL |
> order_id | order_id | 4 | order_id | 20 |
> Using filesort |
> | 5 | DEPENDENT SUBQUERY | inner_table | ref |
> order_id,event_type,o_e | o_e | 261 | order_id | 1 |
> Using where; Using index |
> +----+--------------------+-------------+--------
> +-------------------------+----------+---------+----------+---------
> +--------------------------------+
>
> When I did this EXPLAIN there were no rows in the derived table. When
> I did the time measurements it were about 200 rows.
>

Which, unfortunately, makes the EXPLAIN useless. MySQL makes decisions
based on the data in the tables, also.

> Interestingly it took the server 20 seconds to do the EXPLAIN.
>
> I don't understand the factor 10 in the time difference. It is
> certainly not the number of rows in table "history" as it would be if
> the subquery would be executed for each line in history.
>
>
> From older experiences I have always been sceptical whether MySQL
> caches a derived table or not. I did experiments with many small
> subtables compared to one large subtable and found that often the many
> small subtables are much faster. Is this a matter of available memory?
>
> Regards,
> André


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================