MySQL Forums
Forum List  »  MyISAM

MYISAM INDEX KEY Question
Posted by: lee fei
Date: May 27, 2014 02:49AM

I have the table structure as below:

CREATE TABLE IF NOT EXISTS `users` (
`client_id` varchar(10) NOT NULL,
`user_id` varchar(10) NOT NULL,
`status` enum('N','Y') NOT NULL DEFAULT 'Y',
`password` varchar(50) NOT NULL,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`client_id`,`user_id`),
KEY `index_key1` (`client_id`,`user_id`,`status`,`password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I have 2 keys in my table:
1. PRIMARY (client_id, userid)
2. index_key1 (client_id, user_id, status, password)

When i try to explain following query, I can get the PRIMARY as the index key. This is fine.

EXPLAIN SELECT *
FROM `users`
WHERE `client_id` LIKE 'nanako'
AND `user_id` LIKE ‘jasmine'


id |select_type |table |type |possible_keys |key |key_len| ref |rows |Extra
1 |SIMPLE |users |range |PRIMARY,index_key1 |PRIMARY |24 |NULL |1 |Using where



However, when I try to explain following query, the key that selected still PRIMARY, instead of index_key1 as expected.


EXPLAIN SELECT *
FROM `users`
WHERE `client_id` LIKE 'nanako'
AND `user_id` LIKE ‘jasmine'


id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra
1 |SIMPLE |users |range |PRIMARY,index_key1 |PRIMARY |24 |NULL |1 |Using where


May I know the reasons that the query does not get 'index_key1' as indexing purpose?

Options: ReplyQuote


Subject
Views
Written By
Posted
MYISAM INDEX KEY Question
3346
May 27, 2014 02:49AM
1690
May 28, 2014 12:48AM
1602
May 29, 2014 01:57AM
1594
May 29, 2014 10:44PM


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.