- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.4 Optimizing Database Structure
- 7.4.1 Design Choices
- 7.4.2 Make Your Data as Small as Possible
- 7.4.3 Column Indexes
- 7.4.4 Multiple-Column Indexes
- 7.4.5 How MySQL Uses Indexes
- 7.4.6 The MyISAM Key Cache
- 7.4.7 MyISAM Index Statistics Collection
- 7.4.8 How MySQL Opens and Closes Tables
- 7.4.9 Drawbacks to Creating Many Tables in the Same Database
MySQL keeps row data and index data in separate files. Many (almost all) other database systems mix row and index data in the same file. We believe that the MySQL choice is better for a very wide range of modern systems.
Another way to store the row data is to keep the information for each column in a separate area (examples are SDBM and Focus). This causes a performance hit for every query that accesses more than one column. Because this degenerates so quickly when more than one column is accessed, we believe that this model is not good for general-purpose databases.
The more common case is that the index and data are stored together (as in Oracle/Sybase, et al). In this case, you find the row information at the leaf page of the index. The good thing with this layout is that it, in many cases, depending on how well the index is cached, saves a disk read. The bad things with this layout are:
Table scanning is much slower because you have to read through the indexes to get at the data.
You cannot use only the index table to retrieve data for a query.
You use more space because you must duplicate indexes from the nodes (you cannot store the row in the nodes).
Deletes degenerate the table over time (because indexes in nodes are usually not updated on delete).
It is more difficult to cache only the index data.
User Comments
I believe this page needs an update. InnoDB storage engine doesn't keep indexes and data files in separate files, they're in one huge table space.
> The more common case is that the index and data are stored together (as in Oracle/Sybase, et al)
That's not true for Oracle (!). Since Oracle 8i, you are able to store data and indexes together (Index Organized Table - IOT) in the same structure. And it has always been possible to separate Index and Datas in différents files !
> You cannot use only the index table to retrieve data for a query.
That's what IOT are made for !
Add your own comment.