Sequence gaps in MySQL

Auto-increment sequences are a common way to define a primary key in MySQL. This types of artificial keys, known as surrogate keys are commonly used by developers to quickly construct a database table. A common reason developers use artificial keys is due to the fact that most do not take the time to search for a natural key in their model, especially SQL newbies. The question of whether we should use a surrogate key or a natural key is a debate we should leave to the database experts. The purpose of this post is to present some queries to find if a auto-increment sequence contains gaps.
(more…)

Read More

Multi column sequences in MySQL

One of the most common used attributes in MySQL is definitely AUTO_INCREMENT. This is quite helpful when one needs to generate unique identities for the table rows. By default when a AUTO_INCREMENT column is the only column in a index, whether PRIMARY KEY or UNIQUE, it generates a single monotonic sequence of numbers : 1,2,3,4,… etc. But for MyISAM storage engine it is possible to create complex sequences in a table containing an AUTO_INCREMENT column.
(more…)

Read More