Page 1 of 1

When Is It Good To Use An Index?

Posted: Mon Mar 10, 2008 12:39 pm
by supermike
That may seem like an obvious question, but I thought I read somewhere a long time ago (back in my regrettable M$ days) that it's not always good to just throw an index on a table based on your application's WHERE clauses because it could actually slow it down in some cases, rather than speed it up. MySQL is not MS SQL Server, however. So, for MySQL, what's the best rule of thumb for adding an index on a column?

Re: When Is It Good To Use An Index?

Posted: Wed Mar 12, 2008 9:58 am
by pickle
I throw indexes in when I have a potentially large (in my case, > 20K rows) table & I'm doing way more SELECTs than INSERTs. Indexes will slow down INSERTs as the engine has to write to the index as well as the table, but they do absolute wonders for speeding up SELECTs.

Re: When Is It Good To Use An Index?

Posted: Wed Mar 12, 2008 10:08 am
by CoderGoblin
Couple of points in additional to that previous mentioned

* Obvious but index only columns searched on specifically, not those where results obtained through 'select *'.
* It should be also noted that indexes are best on numeric columns, not text.

Re: When Is It Good To Use An Index?

Posted: Wed Mar 12, 2008 3:01 pm
by aaronhall
Definitely read up on the EXPLAIN query and know it cold. It provides information about how the query planner will execute your query, and makes it easy to determine where indexing will cut down on seek times. There is wealth of information on indexing and query optimization at your local internets.