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?