Has anyone ever successfully done index 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 am 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 done index work for MySQL?

I will be happy to see successful stories here :)

Answer 1

Long back:

show indices with 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 = 50,000

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' 

Composition:

 '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 have earned from 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 2

I have no idea if this is the real reason, but I think that any DBMS worthy of its salt will see the "rows = 3" property and simply decide that you should not even look at the indexes. The speed with which you can perform a full table scan in three rows will make any other way debatable.

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

---

From here , the commentator states that "the table in which I tested led to a version with a join-join-index that does not use any indexes in certain situations", although they do not seem to know what these situations are, namely :-) This is probably something you can enhance with the help of MySQL support groups (and developers).

Just out of interest, which causes the following query from EXPLAIN:

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

And, perhaps, MySQL is evaluating whether to use index based aggregation based on the data inside the table itself. If there are only 2 options a and 3 options b , he can decide again that your query will return most of the rows anyway, so don't worry about optimization.

You can try with both a large number of rows and a large number of values ​​in columns a and b .

Keep in mind that this is not based on my knowledge of MySQL, I have never seen code or used a product. However, I did a bit of work on a specific database product, so this tip is based on how I understand that doing something efficiently, which may be wrong for MySQL, and perhaps it may not be at all: - )

Answer 3

Too bad to cause MySQL not to use merging, for example, how you can force it to use a specific index when it selects the wrong one by default (it is rare, but I saw it and dealt with it).

I assume this is simply because your data does not have enough power for MySQL to decide whether to use an index, not to mention index_merge. 1800 lines - nothing. Create at least a million lines and make each one unique. Then this will probably do what you want. With such a small table, the index really does nothing for you.