MySQL Forums
Forum List  »  Performance

'Compound' INDEXes
Posted by: lee fei
Date: June 04, 2014 03:24AM

Dear Rick,

I am referring to following url that suggested by you =)
http://mysql.rjweb.org/doc.php/index1

I have some questions..
(A) "Covering": INDEX(last_name, first_name, term)" is new to me.
Previously I thought only fields used in WHERE condition need to be indexed.
(QA1) What if I have the query as following:
SELECT term, seq
FROM Presidents
WHERE last_name = 'Johnson'
AND first_name = 'Andrew';
Would you recommend me to have an index key (last_name, first_name, term, seq)?
Same question if "seq" is not a primary key.

(QA2) If I have more than 5 fields to be selected, is it convenient to create a long index key with multiple fields?
SELECY term, age, dob, father_name, mother_name, wife_name
FROM Presidents
WHERE last_name = 'Johnson'
AND first_name - 'Andrew';
INDEX KEY: (last_name, first_name, term, age, dob, father_name, mother_name, wife_name)


(B) Not quite understand with following terms:
⚈ What would happen if you shuffled the fields in the WHERE clause?
Answer: The order of ANDed things does not matter.
⚈ What would happen if you shuffled the fields in the INDEX?
Answer: It may make a huge difference. More in a minute.

(QB1) No matter how i shuffle the fields in the WHERE clause, it should depend on the fields order of the INDEX that I created, is this correct?

Options: ReplyQuote


Subject
Views
Written By
Posted
'Compound' INDEXes
1939
June 04, 2014 03:24AM
935
June 05, 2014 09:12PM


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.