Re: Recreating a dated series from a change log
Rick James Wrote:
-------------------------------------------------------
> With Handler_read_rnd_next = 45 for that query (2
> puc_change rows; 30 T_day rows), I suspect the
> query is O(N*N), hence will not scale well?
I think it is only when Block Nested Loop is used that you will get O(N*N) behavior.
When the puc_change gets bigger the execution plan is as follows:
+----+--------------------+-------------+------------+--------+---------------+---------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------------+------------+--------+---------------+---------+---------+------+------+----------+------------------------------------------------+
| 1 | PRIMARY | d | NULL | range | PRIMARY | PRIMARY | 3 | NULL | 5 | 100.00 | Using where; Using index |
| 1 | PRIMARY | p | NULL | eq_ref | PRIMARY | PRIMARY | 3 | func | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | puc_changes | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 33.33 | Range checked for each record (index map: 0x1) |
+----+--------------------+-------------+------------+--------+---------------+---------+---------+------+------+----------+------------------------------------------------+
'Range checked for each record' means that the optimizer will run the range optimizer on each execution. In this case I would expect the range optimizer to see that it can find the max value with an index lookup on '<= d.T_day'.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway
Subject
Written By
Posted
Re: Recreating a dated series from a change log
April 29, 2015 02:26AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.