MOCKSTACKS
EN
Questions And Answers

More Tutorials









MYSQL Indexes and Keys

Create index


-- Create an index for column 'name' in table 'my_table'
CREATE INDEX idx_name ON my_table(name);

Create unique index


A unique index prevents the insertion of duplicated data in a table. NULL values can be inserted in the columns that form part of the unique index (since, by definition, a NULL value is different from any other value, including another NULL value)

-- Creates a unique index for column 'name' in table 'my_table'
CREATE UNIQUE INDEX idx_name ON my_table(name);

AUTO_INCREMENT key


CREATE TABLE (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 ...
 PRIMARY KEY(id),
 ... );

Main notes:

.Starts with 1 and increments by 1 automatically when you fail to specify it on INSERT, or specify it as NULL.

.The ids are always distinct from each other, but...

.Do not make any assumptions (no gaps, consecutively generated, not reused, etc) about the values of the id other than being unique at any given instant.


Subtle notes:

.On restart of server, the 'next' value is 'computed' as MAX(id)+1.

.If the last operation before shutdown or crash was to delete the highest id, that id may be reused (this is engine-dependent). So, do not trust auto_increments to be permanently unique; they are only unique at any moment.

.For multi-master or clustered solutions, see auto_increment_offset and auto_increment_increment.

.It is OK to have something else as the PRIMARY KEY and simply do INDEX(id). (This is an optimization in some situations.)

.Using the AUTO_INCREMENT as the "PARTITION key" is rarely beneficial; do something different.

.Various operations may "burn" values. This happens when they pre-allocate value(s), then don't use them: INSERT IGNORE (with dup key), REPLACE (which is DELETE plus INSERT) and others. ROLLBACK is another cause for gaps in ids.

.In Replication, you cannot trust ids to arrive at the slave(s) in ascending order. Although ids are assigned in consecutive order, InnoDB statements are sent to slaves in COMMIT order.


Conclusion

In this page (written and validated by ) you learned about MYSQL Indexes and Keys . What's Next? If you are interested in completing MYSQL tutorial, your next topic will be learning about: MYSQL Full Text search.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.