Should I add an index for all fields in the WHERE clause? - MySQL

Question

In my program I have very few inserts, and any which are run frequently are not needed instantly and therefore have been changed to INSERT DELAYED. Should I go through my code and see which fields are referenced in the WHERE clause and add an index for each of them? If so what type of index do I use? Is it just inserts that are slowed down?

Also can I use these indexes on any data type?

Answer 1

There are two major places where we can consider indexing: columns referenced in the WHERE clause and columns used in JOIN clauses. In short, such columns should be indexed against which you are required to search particular records.

• Only index those columns that are required in WHERE and ORDER BY clauses. Indexing columns in abundance will result in some disadvantages.
• Use the NOT NULL attribute for those columns in which you consider the indexing, so that NULL values will never be stored.
• Use the --log-long-format option to log queries that aren’t using indexes. In this way, you can examine this log file and adjust your queries accordingly. Also slow-query log.
• The EXPLAIN statement helps you to reveal that how MySQL will execute a query. It shows how and in what order tables are joined. This can be much useful for determining how to write optimized queries, and whether the columns are needed to be indexed.

This blog is good.