MySQL Forums
Forum List  »  MyISAM

Re: MYISAM INDEX KEY Question
Posted by: Rick James
Date: May 29, 2014 10:44PM

> 1. Can I say query #3 & query #4 are better? Because it involves fewer "rows"

General answer: Sometimes.
Specific answer (for your queries): No.
Further discussion: "Rows" is an estimation generated by the Optimizer; it is rarely exact.

> 1. Can I say query #3 & query #4 are better? Because it involves a smaller "key_len".

No.
Smaller "Key_len" means that it using less of the index. That usually means that it is being less specific.

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

* Those two or three fields are at the beginning ("beginning" may be important) of the indexes.
* PRIMARY KEY is a smaller index (because it has fewer fields).
So, either index is a "good" candidate. I don't know why it picked the one it did. The overall effort is about the same, and it is probably difficult to make a wise decision.

> 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?

Maybe.
For Engine InnoDB (but you are using MyISAM), the PRIMARY KEY fields are tacked onto the end of the secondary key. If the WHERE clause is still hitting the 'beginning' fields of the index, then it is likely to be good to leave off those fields.
But, there are a lot of if's and but's. You don't have a SELECT that demonstrates when it would be 'better'.

> 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)?

Maybe.
* Generally, it is not a good idea to put "long" fields (eg, VARCHAR(100)) into an index.
* A shorter index usually does as good.
* On the other hand, when _all_ of the fields anywhere in the statement are found in an index, you get "Using index", as in Cases #1, #2. This means that the SELECT can be performed by only looking at the index, and not touching the data. (Each index is stored separate from the data and separate from each other. Exception: In InnoDB, the PRIMARY KEY is stored with the data. -- #4 would run faster.)

INDEX`client_id`,`user_id`,`status`,`password`) is a bit awkward -- the first two fields of the index are unique, meaning there is no selectivity in the last two fields.

There are many more things that can be said about indexing; this is just the tip of the iceberg. For example, we have only talked about "=" in the WHERE clause. Things get dicier if you use "<" and other operators.

I hope you learned some things from my comments. Here's another document on indexing:
http://mysql.rjweb.org/doc.php/index1
The first couple of sections of the following give more tips on indexing.
http://mysql.rjweb.org/doc.php/ricksrots

Feel free to pose more EXPLAIN SELECTs and/or INDEXes; I'll be happy to critique and explain them.

Options: ReplyQuote


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