CODEWITHSUNDEEP
mysqloracle-databaseindexing
Amarnath
Amarnath

Reputation: 8865

Database index creation Vs User creating indexes for a table

I have a mysql database. When I enter the data in a table[T_ID, T_Name, T_AGE] (say around 10k rows.) Will the database implicitly create indexes for this table. If so, then what will happen if I create index my-self using "Create Index .. ON say T_ID". Will both the indexes exists or which one will actually be present.

Upvotes: 0

Views: 76

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

If you specify primary key(t_id) then it will create automaticaly an index and you won't be able to create another one on the same column.

why you don't try these cases?

Upvotes: 1

Omesh
Omesh

Reputation: 29101

It depends on storage engines in MySQL.

In case of InnoDB storage engine if you don't specify any PRIMARY KEY on ID column then internally it will automatically create a clustered index. see here.

In case of MyISAM storage engine you can explicitly specify PRIMARY KEY on ID field.

Secondary indexes are optional in both the cases.

Upvotes: 0

Related Questions