When Is It Good To Use An Index?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
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?

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: When Is It Good To Use An Index?

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
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?

Post 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.
User avatar
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?

Post 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.
Post Reply