MySQL Indexes and Constraints


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

–A foreign key is like a check where the data must be in another table, there is no check constraint in MySQL.

show indexes from test_cons2;

–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.

–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.

–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.

Foreign Key Constraints

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.

RESTRICT – changes to data that reference existing foreign keys will be rejected and will not occur in both parent and the child table, for an example you don’t wish to delete any billing records that you need to keep them for tax purposes
CASCADE – changes to data that reference foreign keys will be cascade, if the parent table data is updated the child table data will also be updated, for example a user wishes to their name which you want to be propagated
SET NULL – this is the same as cascade except that when a delete occurs, records in the child table will not be removed, they will be set to NULL instead
NO ACTION – this is the same as restrict

–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.

Index Maintenance

disable/enable keys

alter table tablename disable keys;
alter table tablename enable keys;