CODEWITHSUNDEEP
mysqldatabaseindexingprimary-key
NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5153

MySQL indexing on a non-primary-key column

I am running some MySQL queries on a pretty large table (not on Facebook scale, but around a million rows), and I am finding them very slow. The reason, I suspect, is that I am querying on an id field, but that id has not been declared as primary key, and also no index has been declared.

I cannot set the id field to primary key, because it is not unique, although its cardinality is pretty close to 1. Under these circumstances, if I do a alter table to add an index on the id field, is it supposed to boost up the query speed, given that it is not a primary key?

And supposing it does, how long will it take for the index to develop fully so that the queries start executing quickly? I mean, the moment the prompt appears after executing the alter table, or is it that even though the prompt appears the index building will go on internally for quite some time? (I am asking before doing it because I am not sure whether declaring index on non-unique field corrupts the db or not)

Upvotes: 0

Views: 807

Answers (1)

Barmar
Barmar

Reputation: 780713

Any index will speed up queries that match on the corresponding column. There's no significant difference between the primary key and other indexes in this regard.

The index is created immediately when you execute the ALTER TABLE query. When the prompt returns, the index is there and will be used. There's no corruption while this is happening.

Upvotes: 2

Related Questions