Indexes
SQL theory states that a key is a data constraint, for example a unique key or foreign key, it also provides access to a limited set of data more quickly. Constraints can be applied to a single data field or to more than one data field.
simple key | a key constraint or index applied to one data field |
composite key | a key constraint or index applied to more than one data field |
surrogate key | a unique key that is a meaningless number, normally a ID key |
There are a number of different types of constraints and index which I will discuss throughout this section
constraints | UNIQUE KEY, PRIMARY KEY and FOREIGN KEY |
indexes | INDEX, FULLTEXT INDEX, SPATIAL INDEX you also have UNIQUE KEY and PRIMARY KEY which are also constraints |
To constraint data we use the UNIQUE KEY, PRIMARY KEY and FOREIGN KEY, data is check during an insert or an update. unique keys and primary keys protect against duplicating, the difference being that you can have only one primary key and NULL's are not allowed for a primary key. A foreign key is like a check where the data must be in another table, there is no check constraint in MySQL.
creating constraints | alter table tablename add unique key key_name (col1,col2..) alter table tablename add primary key key_name (column) create unique index index_name on tablename (col1,col2..) Note: you can create constraints when creating the table which is known as in-line - see below examples |
dropping constraints | drop index index_name on tablename alter table tablename drop foreign key key_name |
constraints examples | ## creating the constraints all on one line (known as in-line) ## Now lets use the out-of-line example to create the constraints and give them meaningful names create table other_table ( create table test_cons2 ( ## Seeing the results, first a quick look at the table, notice the key column mysql> desc test_cons2; ## Here you can see in detail the key names what columns the indexes are on and sequence number of the key (if you have a composite key) |
Indexes are data structures, they can either be B-tree, R-tree (only available for indexes on SPATIAL data) or hash data structures. B-tree's are the most common and is available for all storage engines (except NDB), they are excellent for finding data ranges, hash data structures are for finding exact matches or a series of exact matches.
You generally create indexes on the most common fields of a table that are used, if no index exists a full table scan is performed which can be an expensive operation in terms of I/O, using an index reduces the amount of data that has to be search and thus reduces the I/O operation. Think of it in terms of a reading book it's easy to lookup something in the index and find the page number than to search through the whole the book trying to find what you are looking for, hence why lots of books have indexes at the back.
Data constraints are particularly good candidates for indexes, they enable you to perform very quick lookups on data. FULLTEXT INDEX and SPATIAL INDEX create special data structures designed to easily perform text matching and spatial data searching.
Indexes are always kept current in MySQL, when an update, insert or delete occurs within the table the index must change too, so if your table has many writes creating an index may reduce performance, this is the tradeoff for faster data lookups is slower data updates, in other words if the table has lots of write keep the indexes to a minimum. Also check that columns don't have two indexes on the same information, remember both indexes will have to be maintained.
The MySQL optimizer uses the leftmost prefix when it deciding to use an index, what this means is that if you create a composite key your query should contain the leftmost column of the index otherwise the index would not be used, for example lets say you create a composite key with the columns (A,B,C) the optimizer would use the index where the query where clause would have the following
The index would not be used if you query only contained the following in the where clause
Just remember this when creating composite keys, Oracle does have a feature called index skip-scan which can use the rightmost keys.
I have already created some constraints above which as you know will create indexes, now lets create some indexes just for data retrieval performance, in other words that are not for data integrity.
creating | create index employee_idx on employees (fname, lname); ## specifying different index types |
dropping | drop index employee_idx on employees; alter table drop index employee_idx on employees; |
displaying | show indexes from employees; |
A point to remember is that if you using the alter table to create an index it will obtain a write-lock on the entire table, making it unable to be updated for the duration of the alter table statement, this could be a long time depending on the size of the index being created.
All Btree indexes are stored in ascending order only, numbers are stored in numerical order and strings are stored in lexical order according to the string collation being used. The ordering of an index is critical because it is optimized for scanning sequentially, it can only be used to find the next value not the previous value. It is possible to use the DESC keyword when creating an index but it will be ignored and the index created in ascending order. String indexes allow you specify a length for the index value, this is known as an index prefix and may be used with any string type, it is required for blob or text data types.
You change change the behavior on the referenced foreign table (parent) if an update or delete statement tries to change the existing data on the parent table, the server can cascade the change to the corresponding records in the referencing (child) table, or the server can reject the change.
Both on update and on delete are optional, if no option is given then restrict is the default. When referencing a foreign key that key must have an index build on it other you will receive an error, they also must have similar data types. The following requirements are require to create a foreign key
For examples on creating a foreign see above in the constraint examples
There is a limited number of tasks that you can perform on an indexes in MySQL, most of the index maintenance tasks are normally performed on the table itself see Table Maintenance
disable/enable keys | alter table tablename disable keys; alter table tablename enable keys; |