MySQL Forums
Forum List  »  Performance

Re: 'Compound' INDEXes
Posted by: Rick James
Date: June 05, 2014 09:12PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1940
June 04, 2014 03:24AM
Re: 'Compound' INDEXes
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.