CREATE INDEX

This command creates a new named index on a field or group of fields of some existing table. The same task can be done with the help of ALTER TABLE command. Notice that the PRIMARY KEY can be created only with the ALTER TABLE.

create_index
    :    CREATE [UNIQUE] INDEX index_name ON table_name [index_method] index_column_list 

index_column_list
    :	(column_name [(UINT)] [, column_name [(UINT)]]* )

index_method                        -- v5.6
    :   USING {DEFAULT | HASH}

Normally, you create all indexes using CREATE TABLE. In most cases, you can use flag INDEXED to specify that a field should have an index. Then index will get the auto-generated name, which you can obtain using SHOW INDEXES command.

CREATE TABLE tblPerson( fldName VARCHAR(2044) INDEXED );

Sometimes you may need to specify a UNIQUE constraint on few fields, what force DB create a compound index for those fields. The following example will create 3 indexes.

CREATE TABLE tblPerson
( 
     fldFirstName VARCHAR(2044) INDEXED,
     fldLastName  VARCHAR(2044) INDEXED, 
     UNIQUE( fldFirstName, fldLastName )
);

The same can be done with two commands. The second command may happen much later when the table exists and you realize the need in one more index. In this case, you need to specify a custom name for a unique index.

CREATE TABLE tblPerson
( 
     fldFirstName VARCHAR(2044) INDEXED,
     fldLastName  VARCHAR(2044) INDEXED, 
);
 
CREATE UNIQUE INDEX ind_tbpPerson_U_FullName ON tblPErson( fldFirstName, fldLastName );

• if column list contains only one column, then such index in Valentina DB is considered to be on field-level, what you can see in the SHOW INDEXES command. This index also set ON flag fIndexed of the field. For Valentina DB absolutely not matter in which way you will create this one-field index, using flag or using CREATE INDEX command. You can remove this index also in two way: set flag OFF or using DROP INDEX command.

• A column list of the form (col1,col2,…) creates a multiple-column index. Index values are formed by concatenating the values of the given columns. Such an index in Valentina DB considered being on table-level.

CREATE INDEX T1_f1 ON T1( f1 );

CREATE INDEX T1_f3_f2 ON T1( f3, f2 );

CREATE UNIQUE INDEX T1_U_f3_f2 ON T1( f3, f2 );