Re: 'Compound' INDEXes
> Would you recommend me to have an index key (last_name, first_name, term, seq)?
Yes. And No.
Yes -- the SELECT will run faster than if you had only (last_name, first_name, term). This is because the entire SELECT can be performed in the "index", without reaching over to the "data". That 'reach' is an extra step.
No -- Having more indexes and having long indexes both have drawbacks. I can't say whether you "should" or "should not" go to that 4-column index; it depends on a lot of things.
More indexes -- takes more disk; slows down inserts; clogs up caches. (These may or may not be important.)
Longer indexes -- takes more disk; clogs up caches; diminishing returns. (Again, these may or may not be important.)
I suspect that if you had a 'real' example where you were debating between the 3-col (yet inadequate) index and the 4-col (seemingly perfect) index, your benchmarks would say they run at almost identical speed.
> (QA2) If I have more than 5 fields to be selected, is it convenient to create a long index key with multiple fields?
Now you are moving further into the "No" territory, as discussed above.
Another note: If you are using InnoDB (where the PRIMARY KEY is "clustered" with the data), and (last_name, first_name) were the PRIMARY KEY, then there would be no advantage of in adding a longer indexes. (And the "No" arguments become more important.)
In
WHERE a='...' AND b='...'
I am saying that (a='...') and (b='...') are AND'd together.
In the following, I would say that two expressions are being OR'd together:
WHERE a='...' OR b='...'
(AND is commonly used; OR is less common. AND is easier to optimize than OR.)
> (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?
If I understand QB1 correctly, then yes. Here is another way to explain things:
These two WHERE clauses are optimized identically:
WHERE a='...' AND b='...'
WHERE b='...' AND a='...'
If the first field(s) of the INDEX are in the WHERE clause, that is good. That is, the order of the fields in the INDEX can make a difference. (This statement is a simplification; there are a lot more "buts".)
WHERE first_name = '...'
cannot use
INDEX(last_name, first_name)
because it violates the previous paragraph.
Think about a printed list of people's names. Let's say it is ordered by last_name, then first_name (the normal way of sorting such a list). Then let's say you want to find all the people with a certain first_name without knowing the last_name. You would have to scan the entire list.
So, to create the "optimal" INDEX for a given SELECT:
1. Specify any WHERE fields that are tested via "=" (and AND'd together). The order does not matter.
2. Next specify one more field (such as `x` in AND x>5).
3. Optionally, add all the other fields mentioned in the SELECT. (This gives you a 'covering' index.)
(There are lots of Caveats, but those 2-3 steps are often good.)
The tricky part is when you have many different SELECTs. You need to create a 'reasonably small' number of INDEXes that do a 'good enough' job at handling 'all' of the SELECTs. I don't have good guidance for this task. It often involves
* sacrificing "covering", and
* not making "long" indexes.
My advice is aimed at million-row tables. Queries against a thousand-row table are not likely to be noticeable faster/slower with the "perfect" index(es). Still, it is a good idea to get in the habit of building good indexes. Your thousand-row table may grow, with the SELECTs slowing down.