Comments on When To Use Indexes In MySQL

Indexes absolutely slow down inserts because of the overhead required to update the index, but an update might not be hurt.

Updates that don't change indexes won't incur that extra overhead.

An update is actually a combination of a read and a write.  If the read uses the index and the update doesn't change it, the update might even be faster instead of slower.

It is true that for MyISAM or InnoDB, maintaining indexes can be expensive.  But that's not true for all storage engines, or all situations.

For example, if your employees database fits in main memory then maintaining an index is cheap.

Even if your database doesn't fit in memory, there are ways to maintain indexes. For example, Tokutek's storage engine, TokuDB, can maintain indexes in the face of high insertion or update rates.  (Hundreds of thousands of updates or inserts per second on stock hardware (a single magnetic rotating disk, a multicore processor and not much memory.)

Convenient that you work for them no?

Thank you for your article.

However, Indexing is only one factor to speed up read, sorting and filtering operation. There are many variables should be included in mind when configuring MySql start up variables like:

key_buffer,  table_cache,  sort_buffer_size, read_buffer_size,  query_cache_limit and query_cache_size.

 Regarding slow down update and insert incase of many indexes:

The slowness is not humanbeeing notacable unless you have loop of insert or update.

Thank you.

I'd always loved the way you publish your articles. Good luck my dear friend.

Since the speed is relative, I think it would be more comprehensible if the context in which a study was done is specified. Saying that a table is big or small is too vague

This is very useful for me understanding about places where we need index.Thank You :)

very good article, short and clean

thank you

Precise and good explanation!

Really very good explanation of indexing

I'm searching an article about index in MySQL to optimize my apps query speed. And it's really helpful. Easy to understand

super!

Good Article!!

Thanks for the good tutorial, my first hand understanding of how to use index. 

I liked this article. It helps me a lot to understand about the indexing and shorting in database. This article has written in very simple language as well which help to understant clearly. 

"date hired(date)" doesn't work as a table column.

If I were designing a database system and language, I wouldn't just provide optimization mechanisms like indexes. I would provide a semi-automatic way for the system to accumulate some real-world statistics and then make specific recommendations for optimizations and the speedups each is likely to produce. Leaving such decisions to guesswork is shameful. I'd provide an interactive directive to accumulate statistics for a certain number of days and then email me a recommendation for optimizations.