13

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

SELECT * 
  FROM MyTable 
 WHERE intCol = 100;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502

5 Answers5

11

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)

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • This doesn't sound very scientific. We can index strings because verifying it character by character can surely take more time, and so doing it row by row is an O(n^m_i) operation, with n, being the number of rows and, m_i the number of characters for each string i. Furthermore, if I want to look-up an integer, I can just sort the column where they're stored... which, If I'm not mistaken is how look-up tables works?... Please actually correct me if i'm wrong – Imad Mar 21 '19 at 14:51
9

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).
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • "MySQL also only allows one index per SELECT, and has a limited amount of space for indexes" -- NO?! (Or I am just misunderstanding you. Do you want to quote some document that states this perhaps?) – yankee Feb 06 '11 at 21:36
  • @yankee: When you view the EXPLAIN plan, MySQL only can use one index per statement the EXPLAIN sees. A subquery would be a separate SELECT, for example. – OMG Ponies Feb 06 '11 at 21:38
  • @OMG Ponies: Subqueries is harder business. But there are things like joins and wheres with "and" and "or" and there may be multiple columns with multiple indexes involved. – yankee Feb 06 '11 at 21:44
  • 1
    @yankee: JOINs, AND/OR... still a single query. So a composite index (index on more than one column, with caveats) is often a consideration. – OMG Ponies Feb 06 '11 at 21:45
  • @OMG Ponies: select foo from bar left join foobar on (bar.x=foobar.y) where bar.z=123 If all 3 columns in question are being indexed, I say that all 3 will indexes will be used. Do you have a different opinion? – yankee Feb 06 '11 at 21:58
  • @yankee: Separate, non-composite indexes? Then no, the EXPLAIN will only list one of them used per statement it interprets. I'm curious to see your results, I'm very likely oversimplifying. – OMG Ponies Feb 06 '11 at 22:01
  • The sql in my comment is one statement, we agree on that, right? This shows two keys beeing used: CREATE TABLE `test`.`A` ( `a` INT NOT NULL AUTO_INCREMENT, `b` INT NOT NULL, `c` INT NOT NULL, PRIMARY KEY (`a`), INDEX `key_b`(`b`), INDEX `key_c`(`c`) ) ENGINE = MyISAM; /* create table B using the same model and insert some random stuff in both tables */ explain select * from A left join B on (A.b=B.a) where A.c=11 – yankee Feb 06 '11 at 22:18
  • @yankee: To me it's one statement, but I've been consistently stating that it's EXPLAIN that really matters for interpretation. – OMG Ponies Feb 06 '11 at 22:23
3

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.

yankee
  • 38,872
  • 15
  • 103
  • 162
  • 1
    Maybe not on boolean as far as I am informed. E.g. See this: http://stackoverflow.com/questions/10524651/is-there-any-performance-gain-in-indexing-a-boolean-field – dennis Feb 02 '15 at 10:11
0

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.

exus
  • 215
  • 1
  • 10
0

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.

a1ex07
  • 36,826
  • 12
  • 90
  • 103