Re: 500 mil. rows table partitioning

[Rick James]

> I don't quite understand about 'value'. Since it is not in WHERE does it have to be it in index?

Let me provide a simplified example to answer your question.
INDEX(a)
SELECT b FROM tbl WHERE a=123;
The select will execute thus:
1. drill down the BTree index on `a`.
2. Reach over into the "data" to find the row, so it can fetch the value of `b`.

This is slightly different, and more efficient:
INDEX(a,b)
SELECT b FROM tbl WHERE a=123;
The select will execute thus:
1. drill down the BTree index on (a,b), using only the `a` part. Viola! The value of b is sitting right there in the index. No need to reach into the data.

The EXPLAIN plan indicates the latter case by saying "Using index".

Imagine a directory of people, and information about them. The directory has the people in no particular order, but there is an 'index' of names plus page numbers.
Case 1: index contains only last names.
1. flip through the index, looking for Krassovski. The index says "see page 432.
2. Go to page 432 to find the firstname Misha.

Case 2: index contains lastname and firstname.
1. flip through for Krassovski; "Misha" will be right there; no need to go to page 432.

However, if you wanted more than the firstname, you would still have to go to page 432.

Because your table is "Huge", I included this obscure optimization.