From: Michael Widenius Date: October 13 2000 11:08pm Subject: Re: Does someone forget record_count? List-Archive: http://lists.mysql.com/internals/31 Message-Id: <14823.38386.691538.698264@narttu.mysql.fi> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit hi! >>>>> "Paul" == Paul Cadach writes: Paul> Hi, Paul> -----Original Message----- Paul> From: Michael Widenius Paul> To: Paul Cadach Paul> Date: Friday, October 13, 2000 3:31 PM Paul> Subject: Does someone forget record_count? >>>>>>> "Paul" == Paul Cadach writes: >> Paul> Hi, Paul> It's looks like someone forget to multiply s->found_records on record_count Paul> at line 1739 (or around) of sql_select.cc: Paul> if (s->on_expr) Paul> { Paul> tmp=record_count*min(s->found_records,s->worst_seeks); // Can't use read cache Paul> } >> >> tmp should at the above place be the number of seeks you have to do >> find the rows for this level. >> s-> found_rows should either be the number of rows found by an index >> which we compare with a constants or the number of rows in the table. >> >> Why do you think it's wrong? Can you give us an example where tmp >> gets an unreasonable value ? Paul> If it's not wrong so a bug are hidden in SELECT::test_quick_select() because its returns invalid rows numbers. Actually, it doesn't: mysql> select count(*) from alarms where strattr='T'; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (2.56 sec) This is quite close to 3, which is correct in this case. Paul> Also, everywhere before this line tmp is multiplied to record_count when it lets some number of rows: This is a different thing; In this case tmp should be the number of required seeks. mysql> explain select s.stncode,a.created from station s left join alarms a -> on a.stncode=s.stncode and a.strattr='T' group by s.stncode; Paul> +-------+------+---------------+------+---------+------+------+-----------------+ Paul> | table | type | possible_keys | key | key_len | ref | rows | Extra | Paul> +-------+------+---------------+------+---------+------+------+-----------------+ Paul> | s | ALL | NULL | NULL | NULL | NULL | 9 | Using temporary | Paul> | a | ALL | i3,i1 | NULL | NULL | NULL | 2 | | Paul> +-------+------+---------------+------+---------+------+------+-----------------+ Paul> 2 rows in set (0.00 sec) Paul> -------- not optimized query -------- Paul> Second query processes slowly while relation between tables s and a still Paul> exist. Playing with this type of queries points to me that sometime adding Paul> "dummy" condition (like "a.stncode is null or a.stncode is not null") to Paul> where clause improves results, but not every time. Paul> As you can see, in last (non-optimized query) MySQL detects invalid quick rows count. When it tries to use key i3 (which must be used because 9 rows only (grouped into 5 diffirent rows) will be returned by this query from about 33000 rows in total at alarms table), it Paul> at next place have values: Paul> if (best == DBL_MAX || Paul> (tmp + record_count/(double) TIME_FOR_COMPARE*s->found_records < Paul> best + record_count/(double) TIME_FOR_COMPARE*records)) Paul> record_count = 9 (given as argument to find_best() from query estimation on first table) Paul> best = 18 (sets as record_count * 2, sorry, I don't remember where its gets '2' value) Paul> tmp = 2 (sets from s->found_records) Paul> So, previous condition will be true, and reset key usage by setting best_key to 0. This is actually ok; The best_key was reset to get the optimizer to use an buffered full join on the key. 'make_join_select' should notice that tab->quick is set and only scan through the keys in the given range. The bug was that make_join_select() wasn't called because your query didn't have a WHERE clause. I have fixed this by introducing a dummy WHERE clause in this case. The fix will be in 3.23.26. Paul> BTW, how to subscribe internals' list and does MySQL have some description of its internals? Just mail to internals-subscribe@stripped Regards, Monty