maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hello Igor,

I've observed the following to happen with MWL#24 code:

mysql> create table t1(kp1 int, kp2a int, kp2b int, filler char(100), key(kp1,kp2a), key(kp1,kp2b));
Query OK, 0 rows affected (0.06 sec)

# (test.one_k.a runs from 0 to 999)
MariaDB [mwl24a]> insert into t1 select a,a,a, 'filler' from test.one_k;
Query OK, 1000 rows affected (0.31 sec)
Records: 1000  Duplicates: 0  Warnings: 0

MariaDB [mwl24a]> update t1 set kp1=kp1/100
Query OK, 999 rows affected (2.73 sec)
Rows matched: 1000  Changed: 999  Warnings: 0

MariaDB [mwl24a]> explain select * from t1 where (kp1=2 and  kp2b =2);
+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | kp1,kp1_2     | kp1_2 | 10      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------------+------+-------------+
1 row in set (0.01 sec)

MariaDB [mwl24a]> explain select * from t1 where (kp1=2 and  kp2a =2);
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | kp1,kp1_2     | kp1  | 10      | const,const |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+
1 row in set (0.01 sec)

MariaDB [mwl24a]> explain select * from t1 where (kp1=2 and  kp2a =2) or (kp1=2 and  kp2b =2);
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | kp1,kp1_2     | kp1  | 5       | const |   99 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

So, it will still ignore index_merge option if the keys have common prefix.       (*)

If the common part does not start from keypart #0, then it's ok:

MariaDB [mwl24a]> create table t2 (kp1a int, kp1b int, kp2 int, filler char(100), key(kp1a,kp2), key(kp1b,kp2)); 
Query OK, 0 rows affected (0.03 sec)

MariaDB [mwl24a]> insert into t2 select a,a,a, 'filler' from test.one_k;
Query OK, 1000 rows affected (0.15 sec)
Records: 1000  Duplicates: 0  Warnings: 0

MariaDB [mwl24a]> explain select * from t2 where (kp1a=10 or kp1b=10) and kp2=3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: index_merge
possible_keys: kp1a,kp1b
          key: kp1a,kp1b
      key_len: 10,10
          ref: NULL
         rows: 2
        Extra: Using union(kp1a,kp1b); Using where
1 row in set (5.85 sec)


Is the (*) effect intentional?

BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog