Re: Performance regression with sort_intersection in maria-5.1-wl21
In MariaDB-5.1 with scale factor 0.01

id: 1
select_type: SIMPLE
table: lineitem
type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
key: i_l_suppkey_partkey
key_len: 10
ref: NULL
rows: 4009
Extra: Using where; Using index

statistics: Handler_read_next:4003, Handler_read_key: 2
running time as reported by client: 0.00 sec

In maria-5.1-wl21

id: 1
select_type: SIMPLE
table: lineitem
type: range
possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
key: i_l_partkey,i_l_suppkey_partkey
key_len: 5,10
ref: NULL
rows: 241
Extra: Using sort_intersect(i_l_partkey,i_l_suppkey_partkey); Using where

execution time: 0.09

Re: Performance regression with sort_intersection in maria-5.1-wl21
DBT3 with scales 0.1 and 0.01 in mysqldump format is available at

http://bazaar.launchpad.net/~randgen/randgen/rqg2/files/head:/conf/dbt3/

click the arrow button at the right to download the file

Re: Performance regression with sort_intersection in maria-5.1-wl21
To reproduce on a 32-bit machine:

bzr branch lp:~maria-captains/maria/maria-5.1-wl21 bug686355

cd bug686355

./BUILD/compile-pentium-debug-max-no-ndb

wget http://bazaar.launchpad.net/~randgen/randgen/rqg2/download/philips%40eve-20101206145105-t8wwkvi1kbxdxwkn/dbt3s0.01.dump.bz2-20101206144900-b0ku0fait4lahr50-1/dbt3-s0.01.dump.bz2

bzip2 -d dbt3-s0.01.dump.bz2

MTR_VERSION=1 perl mysql-test-run.pl -mysqld=-init-file=/home/philips/bzr/bug686355/dbt3-s0.01.dump --start-and-exit 1st

Then run

SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244);

The plan you should get is

mysql> explain SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244)\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      id: 1
                                                      select_type: SIMPLE
                                                      table: lineitem
                                                      type: range
                                                      possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
                                                      key: i_l_partkey,i_l_suppkey_partkey
                                                      key_len: 5,10
                                                      ref: NULL
                                                      rows: 241
                                                      Extra: Using sort_intersect(i_l_partkey,i_l_suppkey_partkey); Using where
                                                      1 row in set (0.01 sec)

which is slower than the one with sort_intersection=off

Re: Performance regression with sort_intersection in maria-5.1-wl21
With the latest version of the tree maria-5.1-wl21 I had:

MariaDB [test]> select count from lineitem;
----------

count

----------

----------
1 row in set (0.00 sec)

MariaDB [test]> SET SESSION optimizer_switch='index_merge_sort_intersection=on';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> explain SELECT COUNT( l_suppkey ) FROM lineitem WHERE (l_suppkey = 3 AND l_partkey = 255 ) OR (l_partkey BETWEEN 111 AND 244)\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      id: 1
                                                      select_type: SIMPLE
                                                      table: lineitem
                                                      type: range
                                                      possible_keys: i_l_suppkey_partkey,i_l_partkey,i_l_suppkey
                                                      key: i_l_suppkey_partkey
                                                      key_len: 10
                                                      ref: NULL
                                                      rows: 4009
                                                      Extra: Using where; Using index
                                                      1 row in set (0.00 sec)

Launchpad bug id: 686355