MySQL Forums
Forum List  »  Optimizer & Parser

index selection
Posted by: Jordi Prats
Date: August 07, 2008 06:01AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
index selection
5361
August 07, 2008 06:01AM
2362
August 07, 2008 07:07PM
2325
August 30, 2008 11:48AM
2142
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.