(mysql.info.gz) Design

Info Catalog

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Data size

 
 7.4.1 Design Choices
 --------------------
 
 MySQL keeps row data and index data in separate files. Many (almost all)
 other databases 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 will
 cause 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 will 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 can't use only the index table to retrieve data for a query.
 
    * You use more space because you must duplicate indexes from the
      nodes (you can't store the row in the nodes).
 
    * Deletes will degenerate the table over time (because indexes in
      nodes are usually not updated on delete).
 
    * It's harder to cache only the index data.
 

Info Catalog

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Optimizing Database Structure

(mysql.info.gz) Data size