Hi Philip, On Fri, Aug 20, 2010 at 09:12:19AM +0200, Sergey Petrunya wrote:

Please find attached the combined patch of DS-MRR for clustered PKs and key sorting.

The tree is in launchpad and buildbot also: https://code.launchpad.net/~maria-captains/maria/5.3-dsmrr-cpk

and all observed buildbot failures in the tree are known to occur without the new code as well.

The above mentioned tree has DS-MRR improvements that are ready for testing. Overview of new functionality ----------------------------- The tree has implementations of MWL#121, MWL#123, MWL#124, MWL#125. All of those are additional sub-strategies of DS-MRR. They are only applicable when MRR is used by BKA (and not by range access). Checking if new code is used ---------------------------- Execution passes through the new code if the EXPLAIN has tables that have type=[eq_]ref, Extra has 'Using join buffer'. Control ------- Both before and after this work: BKA is not enabled by default, see http://askmonty.org/wiki/Manual:Batched_Key_Access for values of @@join_cache_level that enable it. There is no way to completely turn on/off the new behaviour. However, one can - Run SET join_cache_level=0 and then re-run the query without join buffering, which will give you the correct result (but rows most likely will be in different order). - Run the query with bigger/smaller value of @@join_buffer_size (this is where I've found and fixed a big number of problems already). - Compare with lp:~maria-captains/maria/5.3. 5.3-dsmrr-cpk has been recently merged with 5.3, the only difference between them is the code that needs to be tested. What to test ------------ (first, see above for how to hit the new code). Suppose we have the following query (to fix table names): MariaDB [j3]> explain select * from t0,t1 where t1.a=t0.a; +----+-------------+-------+------+---------------+------+---------+---------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+---------+------+-------------------+ | 1 | SIMPLE | t0 | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | SIMPLE | t1 | ref | key1 | key1 | 5 | j3.t0.a | 1 | Using join buffer | +----+-------------+-------+------+---------------+------+---------+---------+------+-------------------+ 2 rowe in set (0.01 sec) Then, interesting cases to test are: - where for various records of t0, table t1 has zero, one, or multiple matches. - where table t1 is an innodb table and is accessed through [a prefix of a] clustered primary key. - where t1.key1 is a simple/composite key made of different types (but this is more of a checklist test) - where the join buffer is exhausted at various stages of accessing table t1 (need to try with different key sizes, join buffer sizes, and many-table joins) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog