Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the default index type for MySQL?

Tags:

mysql

What indexes do all kinds of indexes created by MySQL default InnoDB store engine create, clustered or non clustered indexes, or are different indexes having different index types? I don't understand the convenience, and I haven't found the answer. Thank you very much.

like image 551
DuZhentong Avatar asked Oct 17 '25 04:10

DuZhentong


1 Answers

Clustered Indexes are the "default" from the perspective of your question, I guess might be a simple answer, but there is no default type of index.

Clustered is the most common type of index, a Clustered Index is most often how the key or id of each row in a table in any relational database (MySQL, Sql Server etc) is indexed.

A Clustered Index is physically ordered on disk, the way you want your data ordered.

The most common example is inserting records into a Table and incrementing the the record key by 1 for each insert, this creates a unique key id for each record inserted and the Clustered Index on this unique key is physically ordered on disk in the order 1,2,3,4 etc for each record inserted.

This example is a Primary Key, a Primary Key is a unique Id for a record in a table, almost always the Primary Key is a clustered index, buy default a Primary Key will be Clustered unless you explicitly express that you want the Primary Key to be Nonclustered

You can only have one Clustered key per table, at least that is the way it is with Sql Server because the table rows are being Physically ordered on the hard disk to the order of your Clustered Index, so it makes sense that it will be very hard to physically order Indexed data in a table in more than one way on disk.

Now a nonclustered index is any Array or Hashtable that maps an Index to your table rows for fast lookup, the Array or Hashtable or other data structure that might be used will be stored on disk, but the records in your table will not be physically ordered according to your nonclustered index.

You can have one Clustered Index along with multiple nonclustered indexes for a given database table.

like image 89
Brian Ogden Avatar answered Oct 18 '25 20:10

Brian Ogden



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!