optimize a mySQL DB using indexes

i was wondering, if i add one index for each field in every table of my DB, will that make my queries run faster? or do i have to analyze my queries and create indexes only when req开发者_开发技巧uired?

---

Adding an index on each column will probably make most of your queries faster, but it's not necessarily the best approach. It is better to tune your indexes to your specific queries, using EXPLAIN and performance measurements to guide you in adding the correct indexes.

In particular you need to understand when you shouldn't index a column, and when you need multi-column indexes.

I would advise reading the MySQL manual for optimization of SELECT statements which explains under what conditions indexes can be used.

---

The more indexes you have, the heavier inserting/updating gets. So it's a tradeoff. The select queries that cannot use an index now will get quicker ofcourse, but if you check what fields you're joining on (or using in a where) you will not trade off that much

(and, ofcourse, there is the disk-space, but most of the time I don't really care bout that: ) )

---

Another point is that MySql can only use a single index for a query, so if your query is

SELECT * FROM table WHERE status = 1 AND col1='foob' AND col2 = 'bar'

MySql will use 1 of the indexes, and filter out the rest reading the data from the table.

If you have queries like this, its better to create a composite index on (status, col1, col2)

---

Adding index on every field in every table is not smart. You should add indexes ONLY on columns that you use in the WHERE clause in select OR on which you sort.

Often, the best results are achieved by using multi-column indexes that are specific to your SQL selects.

There are also a partial indexes with limit on the length of field which can also be used to optimize performance and reduce the index site.

Every unnecessary index will slow down the database during the insert because on every insert, every index has to be updated.

Also the more indexes you have, the more chances you have of data corruption. And lastly, indexes take extra storage space on disk, sometimes a lot of space.

Also MySQL tries to keep indexes in memory. If you have unnecessary indexes, there is a good change MySQL will end up using up the available memory with unnecessary indexes in which case your performance will degrade considerable.

Creating the right kind of indexes is probably the single most important optimization technique. That's why when someone asks something like this I thought it was a joke.

This question can only be asked by someone who have not read a single book on MySQL. Just get a good book and read it, then you will not have to ask questions like this.