20

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?

Keir Simmons
  • 1,634
  • 7
  • 21
  • 37

3 Answers3

27

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.

Jacob
  • 2,041
  • 14
  • 16
5

You should always add an index on any field to be used in a WHERE clause (whether for SELECT, UPDATE, or DELETE). The type of index depends on the type of data in the field and whether you need each row to have a unique value. Generally the default index type (Hash vs. Btree) is best left to the default settings unless you really know what you are doing.

Now whether you use individual indexes (one per field) or compound indexes kind of depends on how the application works and is a more advanced subject, so in general if just getting started just use and index for each field. Note that your primary keys automatically have indexes so you don't need to create another index on those.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
1

Don't go changing your queries before you see a performance problem - that's premature optimization. Instead, use MySQL query log to see which queries are taking a long time, and concentrate on improving those queries.

Alex Weinstein
  • 9,823
  • 9
  • 42
  • 59
  • I'm still in the process of developing my website, so it seems a better idea to release it as optimised as possible rather than having to come back and fix it later. – Keir Simmons Jul 27 '12 at 19:53
  • 1
    I would discourage you from premature optimization even at this phase. Test out the performance of your site by using it yourself - or having friends use it. Then see what queries are slow in the query log. If you can, launch early, instead of doing this kind of optimization. – Alex Weinstein Jul 27 '12 at 19:54
  • 5
    Adding indexes to fields used in WHERE clauses is not premature optimization. That is database design 101. It should ALWAYS be a consideration when you are determining your table structure. – Mike Brant Jul 27 '12 at 19:55
  • 1
    The queries are already written. Instead of spending time on doing this - which will not likely not have user benefit - I'd concentrate on doing something that actually will help his app succeed. – Alex Weinstein Jul 27 '12 at 20:50
  • 1
    Two different opinions :-) As a data engineer, I agree that indexes used in WHERE should be there from the beginning. As a full-stack web developer... who the hell has time to go back and forth between front-end and DB optimization, let's leave it for later when we can use planner and optimizer and do it ONCE. If you are alone or with limited resources, use Alex's approach. As a part of a team: if you are a data engineer run frequent logs and make sure that those indices are there right away when you notice. If you are app developer - inform your database team about new queries you create. – Peter Majko Oct 31 '19 at 14:16