views:

673

answers:

1

my question is , when I create an index for a table in mysql, I see that the index_type is type BTREE . Now although I understand about btree(s), I do not quiet understand how it stores the index and how the database searches the records based on this. I mean, btree is excellent for databases to perform read and writes large blocks of data, when we create an index for column type of Primary key, what I understand is, it creates a tree and splitting the values for the root based on the value type of the root. Now, does it store only the the primary key ID under the trees or the whole data associoated with that primary key?

after finding the wanted primary ID, how does the database extract the record?

Hope I am clear.

Thanks.

+2  A: 

The database stores the value indexed as a B-Tree key, and the record pointer as a B-Tree value.

Whenever you search for a record holding a certain value of an indexed column, the engine locates the key holding this value in the B-Tree, retrieves the pointer to the record and fetches the record.

What exactly is a "record pointer", depends on the storage engine.

  • In MyISAM, the record pointer is an offset to the record in the MYI file.

  • In InnoDB, the record pointer is the value of the PRIMARY KEY.

In InnoDB, the table itself is a B-Tree with a PRIMARY KEY as a B-Tree key. This is what called a "clustered index" or "index-organized table". In this case, all other fields are stored as a B-Tree value.

In MyISAM, the records are stored without any special order. This is called "heap storage".

Quassnoi
Thanks for the explanation. In my database i have some tables with MyISAM and some as InnoDB with indexes on them. Will this in anyway effect the joins and queries I write is both are different types?
JPro
@JPro: The database handles the underlying indexing details automatically. The `JOIN` syntax is the same for `MyISAM` and `InnoDB` tables.
Quassnoi
what i mean is, does it matter at all to create all the tables with same storage engine or not?
JPro
@JPro: you can mix the different engines in one query freely. Be aware, however, that `MyISAM` has no transaction support and when using multiple tables in a same `DML` query which you later rollback, be aware that the rollback will not affect changes to `MyISAM` tables. But, again, in a `SELECT` query, it's perfectly ok to mix both engines.
Quassnoi
just final question. I see in my mysql folder, 2 types of files (.frm and .myd), .myd files are bigger than .frm files. I guess .frm used by btrees. How does the data gets stored in to these file ? If the database wants to search any record, does it just open the file and search the tree?
JPro
@JPro: `FRM` is the table description, is contains no data. For `MyISAM`, the table data are contained in `MYD` fileds, the index data are contained in `MYI` files. For `InnoDB`, the table data are contained in the `InnoDB` tablespaces, which can be shared (all tables and indexes reside in a single file in the root of the data directory), or per-table (table and all of its indexes reside in a single `ibd` file in the database directory)
Quassnoi