Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For example:
CREATE TABLE test(
col_1 INT,
col_2 INT,
col_3 VARCHAR(11),
col_4 VARCHAR(11) )ENGINE = INNODB;

CREATE INDEX mul ON test(col_2,col_3,col_4);

Sorry,my English is not good,I mean create a multi-column index, what is this index structure?

share|improve this question

closed as unclear what you're asking by swasheck, Mark Storey-Smith, Kin, Max Vernon, Mike Walsh Nov 20 '13 at 19:15

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
what are you asking? "b+tree of the 'mul' index is how to arrange the data?" is unclear. It sounds like you are either asking how a B-Tree is designed or perhaps you are asking if your index uses a B-Tree. –  Max Vernon Nov 21 '13 at 14:38

1 Answer 1

Note: At the time this answer was written, the question was:

In a B-Tree index, are all columns of a single row stored together, or are the columns stored in separate structures?


Most RDBMSs (including MySQL) implement indexes as B+Tree not B-Tree. There are two types of B+Tree indexes: Clustered and Non-Clustered.

Both have in common that each intermediate level (including the root) of the index contains all columns that make up the key. The leaf level contains all key columns plus additional information.

In a clustered index, that additional information are all other columns of that table. For non-Clustered indexes, that additional information contains at least a reference to the actual table row. Some RDBMSs allow to also add additional columns here, so called "included" columns.

Either way, a B+Tree always contains the complete tuple relevant to the current index level within a single B-Tree node (which is usually equivalent to a data page. This storage organization is also called the "N-ary Storage Model" or NSM.

Some RDBMSs allow for column oriented storage, like SQL Server with the Column Store Index. That model is called the "Decomposition Storage Model" or DMS. However, a structure using the DSM is not organized as a B+Tree (or even a B-Tree).

There is a third form of storage that combines the two ideas. It is called PAX (partition attributes across). However it is rarely implemented.

References:

http://en.wikipedia.org/wiki/B%2BTree

http://www.pdl.cmu.edu/ftp/Database/pax.pdf

http://en.wikipedia.org/wiki/Column-oriented_DBMS

share|improve this answer
    
Thank you very much! It seems I need a time to digest. –  thou95 Nov 19 '13 at 5:30

Not the answer you're looking for? Browse other questions tagged or ask your own question.