MySQL Forums :: Optimizer & Parser :: index selection


Advanced Search

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 5185 Jordi Prats 08/07/2008 06:01AM
Re: index selection 2344 KimSeong Loh 08/07/2008 07:07PM
Re: index selection 2299 Shlomi Noach 08/30/2008 11:48AM
Re: index selection 2116 Rick James 08/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.