index selection
Hi all!
I've two keys in a table which one key is prefix of the other one. Why sometimes the optimizer chosses one and other times the other one?
Here's what I have:
mysql> show index from test \G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: b
Collation: A
Cardinality: 420801
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 3. row ***************************
Table: test
Non_unique: 1
Key_name: idx_a
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 60114
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
3 rows in set (0.00 sec)
This explains show the diferent index chossen:
mysql> explain select * from tbl_fotos_apartamentos where fk_id_tbl_apartamentos=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: PRIMARY,idx_a
key: PRIMARY
key_len: 4
ref: const
rows: 9
Extra:
1 row in set (0.00 sec)
mysql> explain select * from tbl_fotos_apartamentos where fk_id_tbl_apartamentos=123\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: PRIMARY,idx_a
key: idx_a
key_len: 4
ref: const
rows: 15
Extra:
1 row in set (0.00 sec)
regards!
Jordi
Subject
Views
Written By
Posted
index selection
6453
August 07, 2008 06:01AM
2594
August 07, 2008 07:07PM
2629
August 30, 2008 11:48AM
2476
August 31, 2008 11:16PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.