mysql index on column of int type

If a column is of type int, does it still need to be indexed to make select query run faster?

SELECT * 
  F开发者_StackOverflowROM MyTable 
 WHERE intCol = 100;

---

Probably yes, unless

• The table has very few rows (<1000)
• The int column has poor selectivity
• a large proportion of the table is being returned (say > 1%)

In which case, a table scan might make more sense anyway, and the optimiser may choose to do one. In most cases having an index anyway is not very harmful, but you should definitely try it and see (in your lab, on production-grade hardware with a production-like data set)

---

Yes, an index on any column can make the query perform faster regardless of data type. The data itself is what matters -- no point in using an index if there are only two values currently in the system.

Also be aware that:

• the presence of an index doesn't ensure it will be used -- table statistics need to be current, but it really depends on the query.
• MySQL also only allows one index per SELECT, and has a limited amount of space for indexes (limit dependent on engine).

---

Yes it does. It does not matter which data type a column has. If you don't specify an index, mysql has to scan the whole table anytime you are searching a value with that column.

---

Yes, the whole point on indexes is that you create them by primary key or by adding the index manualy. The type of the column does not say anything about the speed of query.

---

Yes, it has to be indexed. It doesn't make sense to index tinyint columns if they are used as boolean, because this index won't be selective enough.