How to use Mysql index

This article mainly shows you "how to use the Mysql index", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use the Mysql index" this article.

Select.... From table where key_part1='xxx' and key_part3='yyy'

In this case, MYSQL can only process key_par1 in the index, but filter the condition of key_part3 in the index, unless select is followed by count (*)

This is the table structure of the last test:

CREATE TABLE `im_message_201005_21_ old` (

`msg_ id`bigint (20) NOT NULL default'0'

`time`datetime NOT NULL

`owner` varchar (64) NOT NULL

`other` varchar (64) NOT NULL

`content` varchar (8000) default NULL

PRIMARY KEY (`msg_ id`)

KEY `im_msg_own_oth_tim_ ind` (`owner`, `other`, `time`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

What we are going to test this time is whether MYSQL can use the index correctly to filter invalid data with the condition of range field first.

First of all, let's adjust the order of the index: KEY `im_msg_own_tim_oth_ ind` (`owner`, `time`, `other`)

What we are going to test is how the index works when the where condition is: owner+time+other.

Why don't you come to a conclusion based on your own knowledge? )

I think most of the students think that the fields are the same and the index should work properly. Actually, it's not.

The key to this test is to see if mysql can filter the other field before returning to the table when the query condition is owner+time+other.

If it cannot be filtered, it will produce the same performance (logical read) as if the condition is owner+time.

Select count (distinct content) from im_message_201005_21_old

Where owner = 'cntaobaoytazy' and time > =' 2010-05-23 17-and time 14-23 'and time =' 2010-05-23 17-14-14-14-23 and time = '2010-05-23 17-14 14-23 14-23 and time =' 2010-05-23 17-14 and time 1423 'and time