Documentation Home
MySQL Internals Manual
Download this Manual
EPUB - 0.8Mb

MySQL Internals Manual  /  ...  /  Getting Index Information During CREATE TABLE Operations

22.16.2 Getting Index Information During CREATE TABLE Operations

It is preferable for storage engines that support indexing to read the index information provided during a CREATE TABLE operation and store it for future use. The reasoning behind this is that the index information is most readily available during table and index creation and is not as easily retrieved afterward.

The table index information is contained within the key_info structure of the TABLE argument of the create() method.

Within the key_info structure there is a flag that defines index behavior:

#define HA_NOSAME             1  /* Set if not duplicated records   */
 #define HA_PACK_KEY           2  /* Pack string key to previous key */
 #define HA_AUTO_KEY           16
 #define HA_BINARY_PACK_KEY    32 /* Packing of all keys to prev key */
 #define HA_FULLTEXT          128 /* For full-text search            */
 #define HA_UNIQUE_CHECK      256 /* Check the key for uniqueness    */
 #define HA_SPATIAL          1024 /* For spatial search              */
 #define HA_NULL_ARE_EQUAL   2048 /* NULL in key are cmp as equal    */
 #define HA_GENERATED_KEY    8192 /* Automatically generated key     */

In addition to the flag, there is an enumerator named algorithm that specifies the desired index type:

enum ha_key_alg {
  HA_KEY_ALG_UNDEF=     0,  /* Not specified (old file)     */
  HA_KEY_ALG_BTREE=     1,  /* B-tree, default one          */
  HA_KEY_ALG_RTREE=     2,  /* R-tree, for spatial searches */
  HA_KEY_ALG_HASH=      3,  /* HASH keys (HEAP tables)      */
  HA_KEY_ALG_FULLTEXT=  4   /* FULLTEXT (MyISAM tables)     */

In addition to the flag and algorithm, there is an array of key_part elements that describe the individual parts of a composite key.

The key parts define the field associated with the key part, whether the key should be packed, and the data type and length of the index part. See for an example of how this information is parsed.

As an alternative, a storage engine can instead read index information from the TABLE structure of the handler during each operation.

Download this Manual
EPUB - 0.8Mb
User Comments
Sign Up Login You must be logged in to post a comment.