MySQL Forums
Forum List  »  Newbie

Re: Recreating a dated series from a change log
Posted by: Øystein Grøvlen
Date: April 29, 2015 02:26AM

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

Options: ReplyQuote




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.