Has anyone ever successfully make index merge work for MySQL?

Question

Setup:

mysql> create table t(a integer unsigned,b integer unsigned);
mysql> insert into t(a,b) values (1,2),(1,3),(2,4);
mysql> create index i_t_a on t(a);
mysql> create index i_t_b on t(b);
mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | i_t_a,i_t_b   | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Is there something I'm missing?

Update

mysql> explain select * from t where a=1 or b=4;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | i_t_a,i_t_b   | NULL | NULL    | NULL | 1863 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Version:

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.1.36-community-log |
+----------------------+

Has anyone ever successfully make index merge work for MySQL?

I'll be glad to see successful stories here:)

Answer 1

I have no idea if this is the actual reason but I would think any DBMS worth its salt would see the "rows=3" property and just decide that it's not worth even looking at the indexes. The speed at which you can do a full table scan on three rows would make any other method moot.

Try to do the same thing with a few thousand rows and see if you get the same results.

---

From here, a commenter states that "the table I tested with resulted in the index-merge-union version not using any indexes in certain situations" although they don't seem to know what those situations are, exactly :-) That's probably something you can raise with the MySQL support groups (and developers) as well.

Just out of interest, what does the following query give you from EXPLAIN:

select * from t where a=1
union
select * from t where b=4;

And it may be that MySQL is evaluating whether to use index-union based on the data within the table itself. If there are only 2 variants of a and 3 variants of b, it may again decide that your query will return a large proportion of the rows anyway, so not bother with optimization.

You could try with both a large number of rows and a large variety of values in both a and b columns.

Keep in mind this is not based on my knowledge of MySQL, I've never seen the codebase or used the product. However, I have done a bit of work on a certain mainstream database product - so this advice is based on how I understand to do things efficiently, which may not be the case for MySQL specifically, and indeed may not be the case at all generally :-)

Answer 2

Long back:

show indexes from lesssong;

Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment
'lesssong', 0, 'PRIMARY', 1, 'S_ID', 'A', 50000, , '', '', 'BTREE', ''
'lesssong', 1, 'idx_s_name', 1, 'S_NAME', 'A', 25000, 10, '', '', 'BTREE', ''
'lesssong', 1, 'idx_S_ARID', 1, 'S_ARID', 'A', 1315, , '', '', 'BTREE', ''
'lesssong', 1, 'idxFTS', 1, 'S_NAME', '', 1, , '', '', 'FULLTEXT', ''

Count = 50000

explain select * from lesssong where s_name='kv' or s_arid=4

1, 'SIMPLE', 'lesssong', 'index_merge', 'idx_s_name,idx_S_ARID,idxFTS', 'idx_s_name,idx_S_ARID', '12,4', '', 2, 'Using sort_union(idx_s_name,idx_S_ARID); Using where'

Structure:

'S_ID', 'int(10) unsigned', 'NO', 'PRI', '', 'auto_increment'
'S_ALID', 'int(10) unsigned', 'NO', '', '', ''
'S_ARID', 'int(10) unsigned', 'NO', 'MUL', '', ''
'S_NAME', 'varchar(100)', 'NO', 'MUL', '', ''
'S_LYRIC', 'text', 'NO', '', '', ''
'S_WRITER', 'varchar(45)', 'NO', '', '', ''
'S_LINK', 'varchar(255)', 'NO', '', '', ''

Even for you structure I got it work for me:

I added random 100 values:

insert into t(a,b) select ceil(rand()*5),ceil(rand()*30)

explain select * from t where a=1 or b=4;

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, 'SIMPLE', 't', 'index_merge', 'i_t_a,i_t_b', 'i_t_a,i_t_b', '5,5', '', 32, 'Using union(i_t_a,i_t_b); Using where'

Answer 3

It's too bad there's not a way to force MySQL to use the merge, like how you can force it to use a particular index when it's choosing the wrong one by default (happens rarely but I've seen it and had to deal with it).

I'm guessing it's simply because your data does not have enough cardinality for MySQL to decide it's worth its time to use an index, let alone an index_merge. 1800 rows is nothing - really. Create at least a million rows, and make every single one of them unique. Then it will probably do what you want. With such a small table an index doesn't really do anything for you.