When Is It Good To Use An Index?
Moderator: General Moderators
-
supermike
- Forum Contributor
- Posts: 193
- Joined: Tue Feb 28, 2006 8:30 pm
- Location: Somewhere in the Desert, USA
When Is It Good To Use An Index?
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?
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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
Re: When Is It Good To Use An Index?
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.
* 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.
- aaronhall
- DevNet Resident
- Posts: 1040
- Joined: Tue Aug 13, 2002 5:10 pm
- Location: Back in Phoenix, missing the microbrews
- Contact:
Re: When Is It Good To Use An Index?
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.