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?