MySql Index better performance using a particular data type

Question

Does the performance of mysql index depend on datatypes

If it does, what are the best datatypes to use in such cases?

Also, is it important to keep an index on a column which has less duplicate values or it is going to give the same performance even if applied on a column which has enormous duplicate values.

Thanks in advance!

Answer 1

Indexes aside, the best datatypes are the smallest ones to meet the domain requirements.

Column datatypes do affect the performance of indexes on those columns.

Smaller is better.

Indexes on integer columns work best (smaller, faster).

In a particular instance, the optimiser may chose not to use an index if it is not selective enough (i.e. column has many repeated values, and the value searched for, determined from statistics, will result in 'too many' rows.

• How MySQL Uses Indexes

• Data Type Storage Requirements

From the link @nani1216 posted:

The size of the column or columns you’re indexing is relevant because the less data the database server has to search through or index the faster it’ll be and the less storage you’ll use on disk [or in memory]

Answer 2

Yes the performance varies with the use of datatypes. Indexing on integer datatype gives you more performance than indexing on char or varchar datatypes.

Have a look at What makes a good MySQL index?