File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Indexing MySQL columns Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Indexing MySQL columns" Watch "Indexing MySQL columns" New topic

Indexing MySQL columns

Ali Ekber
Ranch Hand

Joined: Jun 12, 2005
Posts: 41
I have about 8K rows in a big table which wasn't originally indexed. I added 3 indexed and the performance improved drastically. My questions is, will these indexes affect the future insertions into this table? Thx.
Kenneth Albertson
Ranch Hand

Joined: Sep 18, 2005
Posts: 59
Indexes effectively transfer work from output time to input time, so the answer to your question is yes, the indexes will slow down inserts. It is always a trade-off, and the ideal solution depends on your application. If there are lots of inserts, and they they have to be lightning fast, you might be better of with no indexes and slow queries, but that would obviously be an unusual situation. In most situations the inserts with indexes will be ok.

There are even situations where it is best to speed up inserts by minimizing the number of indexes, but come reporting time you build a bunch of indexes, run your reports, and then drop the indexes again.

And these comments are not specific to MySQL.
I agree. Here's the link:
subject: Indexing MySQL columns
It's not a secret anymore!