MySQL Forums
Forum List  »  Partitioning

Re: 500 mil. rows table partitioning
Posted by: Rick James
Date: July 30, 2011 06:14PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: 500 mil. rows table partitioning
1611
July 30, 2011 06:14PM


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.