MySQL Forums
Forum List  »  MyISAM

Re: MYISAM INDEX KEY Question
Posted by: lee fei
Date: May 29, 2014 01:57AM

#1 mysql> EXPLAIN SELECT client_id, user_id
-> 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 | index | PRIMARY,index_key1 | PRIMARY | 24 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


#2 mysql> EXPLAIN SELECT client_id, user_id,
-> PASSWORD
-> 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 | index | PRIMARY,index_key1 | index_key1 | 77 | NULL | 3 | Using where; Using index |
+----+-------------+-------+-------+--------------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


#3 mysql> EXPLAIN SELECT client_id, user_id, description
-> 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 |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

#4 mysql> 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 |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


#5 mysql> EXPLAIN SELECT *
-> FROM `users`
-> WHERE `client_id` LIKE 'nanako'
-> AND `user_id` LIKE 'jasmine'
-> AND STATUS = 'Y'
-> AND Description = 'abc' ;
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
| 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 |
+----+-------------+-------+-------+--------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)


I have some questions:
1. Can I say query #3 & query #4 are better? Because it involves less "rows" and "key_len".

2. For query #5, I thought it supposes to select INDEX_KEY1 for indexing purpose, but it uses PRIMARY instead..

3. During my research recently, if I have PRIMARY KEY of client_id & user_id, then i do not need to include client_id + user_id into other index keys. Is this correct? Says my WHERE condition involves client_id + user_id + status + description, do I actually have to create the index key: INDEX_KEY1 (client_id + user_id + status + description)?

Options: ReplyQuote


Subject
Views
Written By
Posted
3106
May 27, 2014 02:49AM
1586
May 28, 2014 12:48AM
Re: MYISAM INDEX KEY Question
1503
May 29, 2014 01:57AM
1487
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.