MySQL Indexes and Keys

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Syntax

  • -- Create simple index

    CREATE INDEX index_name ON table_name(column_name1 [, column_name2, ...])

  • -- Create unique index

    CREATE UNIQUE INDEX index_name ON table_name(column_name1 [, column_name2, ...]

  • -- Drop index

    DROP INDEX index_name ON tbl_name [algorithm_option | lock_option] ...

    algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY}

    lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Remarks

Concepts

An index in a MySQL table works like an index in a book.

Let's say you have a book about databases and you want to find some information about, say, storage. Without an index (assuming no other aid, such as a table of contents) you'd have to go through the pages one by one, until you found the topic (that's a "full table scan"). On the other hand, an index has a list of keywords, so you'd consult the index and see that storage is mentioned on pages 113-120, 231, and 354. Then you could flip to those pages directly, without searching (that's a search with an index, somewhat faster).

Of course, the usefulness of the index depends on many things - a few examples, using the simile above:

  • If you had a book on databases and indexed the word "database", you might see that it's mentioned on pages 1-59, 61-290, and 292-400. That's a lot of pages, and in such a case, the index is not much help and it might be faster to go through the pages one by one. (In a database, this is "poor selectivity".)
  • For a 10-page book, it makes no sense to make an index, as you may end up with a 10-page book prefixed by a 5-page index, which is just silly - just scan the 10 pages and be done with it.
  • The index also needs to be useful - there's generally no point to indexing, for example, the frequency of the letter "L" per page.


Got any MySQL Question?