Some Information about database indexes
Guidelines to create indexes
The characteristics of data and the usage of the data in the environment in which it is defined ascertains the columns to build the index. The usefulness of the index is directly related to the percentage of rows returned from a query. Low percentage or high selectivity are more efficient. Creating indexes on the frequently searched column is more useful and efficient. Indexes can be best created on the following columns.
Primary Keys
Foreign Keys or columns that are frequently used in joining tables. Columns that are searched for range of key values. Columns that are accessed in sorted order.
An index is not needed for the following columns. A column that is infrequently referenced in a query. A column that contains lots of duplicate values. A column that contains bitmaps, text or images.
Clustered Index
A clustered index is the one in which the order of the values in the index is the same as the order of the data stored in the table. The index is created in actual data pages. The order of values in the index pages is also ascending. The maximum number of clustered indexes for a table is 249. These indexes are best created on columns of high selectivity to unique. Accessing data using clustered indexes is quicker than using non-clustered index.
Using Clustered Indexes
Clustered indexes are used in the column which is most frequently used for ranges of key values or are accessed in sorted order. The following facts are to be remembered about creating clustered index. A table can have only one clustered index. The physical row order of the table and the indexed row are both the same in clustered index. A clustered index must be created before any non clustered index is created, as clustered index changes the physical order of the table. Rows are stored into sequenced order and maintained in that order. Uniqueness of key value is maintained explicitly with the UNIQUE keyword, or implicitly with an internal unique identifier. These unique identifiers are not accessible to the users. The size of the clustered index is about five percent of the table size. However, clustered index size varies depending on the size of the indexed column. When a row is deleted, the space is reclaimed and is available for a new row. A clustered index needs around 1.2 times the table size when the index is created. This space is automatically de-allocated after the process of creating index is over.
Limitations of MySQL
General limitations of MySQL
32-bit binaries cannot addess more than 4 GB of memory. This is not a MySQL limitation, this is a technical limitation.
In MySQL 4.1, the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.
Limits of the MyISAM storage engine
There is a limit of 232 (~4.2 Mia) rows in a MyISAM table. You can increase this limitation if you build MySQL with the --with-big-tables option then the row limitation is increased to 264 (1.8 * 1019) rows.
Limits of MySQL 5.0
Limits of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. This also applies to LEFT and RIGHT OUTER JOINS.
Limits of the MyISAM storage engine
Large files up to 63-bit file length are supported.
There is a limit of 264 (1.8 * 1019) rows in a MyISAM table.
The maximum number of indexes per MyISAM table is 64. You can configure the build by invoking configure with the --with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less thann or equal to 128.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can be changed by changing the source and recompiling.
The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
The maximum row length, except for VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB.
Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR columns with a combined size larger than 65535.
The maximum tablespace size is 4 Mia database pages (64TB). This is also the maximum size for a table.
Limits of MySQL 5.1
Limits of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.
Limits of Partitions
The limit of partitions with MySQL is 1024 (internal mail). But one have to increase open_files_limit
Limits of MySQL Cluster
Max attributes columns in an index: 32
Max number of attributes (columns and indexes) in a table: 128
Max number of table: 1792 (v5.0)
Max size in bytes of a tuple: 8052 byte, excluding blobs which are stored separately.
Max number of nodes in a cluster: 63, max. number of data nodes: 48 (in v5.0/5.1)
Max number of nodes in a cluster: 255 in CGE.
Max number of metadata objects: 20320.
Max atribute name length: 31 characters.
Max database table name length: 122
The characteristics of data and the usage of the data in the environment in which it is defined ascertains the columns to build the index. The usefulness of the index is directly related to the percentage of rows returned from a query. Low percentage or high selectivity are more efficient. Creating indexes on the frequently searched column is more useful and efficient. Indexes can be best created on the following columns.
Primary Keys
Foreign Keys or columns that are frequently used in joining tables. Columns that are searched for range of key values. Columns that are accessed in sorted order.
An index is not needed for the following columns. A column that is infrequently referenced in a query. A column that contains lots of duplicate values. A column that contains bitmaps, text or images.
Clustered Index
A clustered index is the one in which the order of the values in the index is the same as the order of the data stored in the table. The index is created in actual data pages. The order of values in the index pages is also ascending. The maximum number of clustered indexes for a table is 249. These indexes are best created on columns of high selectivity to unique. Accessing data using clustered indexes is quicker than using non-clustered index.
Using Clustered Indexes
Clustered indexes are used in the column which is most frequently used for ranges of key values or are accessed in sorted order. The following facts are to be remembered about creating clustered index. A table can have only one clustered index. The physical row order of the table and the indexed row are both the same in clustered index. A clustered index must be created before any non clustered index is created, as clustered index changes the physical order of the table. Rows are stored into sequenced order and maintained in that order. Uniqueness of key value is maintained explicitly with the UNIQUE keyword, or implicitly with an internal unique identifier. These unique identifiers are not accessible to the users. The size of the clustered index is about five percent of the table size. However, clustered index size varies depending on the size of the indexed column. When a row is deleted, the space is reclaimed and is available for a new row. A clustered index needs around 1.2 times the table size when the index is created. This space is automatically de-allocated after the process of creating index is over.
Limitations of MySQL
General limitations of MySQL
32-bit binaries cannot addess more than 4 GB of memory. This is not a MySQL limitation, this is a technical limitation.
Limits of MySQL 4.1
Limits of JoinsIn MySQL 4.1, the maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.
Limits of the MyISAM storage engine
There is a limit of 232 (~4.2 Mia) rows in a MyISAM table. You can increase this limitation if you build MySQL with the --with-big-tables option then the row limitation is increased to 264 (1.8 * 1019) rows.
Limits of MySQL 5.0
Limits of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view. This also applies to LEFT and RIGHT OUTER JOINS.
Limits of the MyISAM storage engine
Large files up to 63-bit file length are supported.
There is a limit of 264 (1.8 * 1019) rows in a MyISAM table.
The maximum number of indexes per MyISAM table is 64. You can configure the build by invoking configure with the --with-max-indexes=N option, where N is the maximum number of indexes to permit per MyISAM table. N must be less thann or equal to 128.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can be changed by changing the source and recompiling.
Limits of the InnoDB storage engine
A table cannot contain more than 1000 columns.The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
The maximum row length, except for VARCHAR, BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB.
Although InnoDB supports row sizes larger than 65535 internally, you cannot define a row containing VARCHAR columns with a combined size larger than 65535.
The maximum tablespace size is 4 Mia database pages (64TB). This is also the maximum size for a table.
Limits of MySQL 5.1
Limits of Joins
The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.
Limits of Partitions
The limit of partitions with MySQL is 1024 (internal mail). But one have to increase open_files_limit
Limits of MySQL Cluster
Max attributes columns in an index: 32
Max number of attributes (columns and indexes) in a table: 128
Max number of table: 1792 (v5.0)
Max size in bytes of a tuple: 8052 byte, excluding blobs which are stored separately.
Max number of nodes in a cluster: 63, max. number of data nodes: 48 (in v5.0/5.1)
Max number of nodes in a cluster: 255 in CGE.
Max number of metadata objects: 20320.
Max atribute name length: 31 characters.
Max database table name length: 122
Comments
Post a Comment