0

If I understand correctly, indexing a column will increase SELECT speed but decrease INSERT speed.

Say your app is new and you have identified columns which are good candidates for indexing (based on how you did your views e.g., WHERE / JOINS).

Do you have to immediately index those columns even before you notice performance issues? Or does it make sense to do them later as to not affect the INSERTs yet?

Improve this question
3
  • 1
    In most cases INSERT is interactive single record insert. So it doesn't matter is the insert process delayed by the index or not. Think about INSERT performance decrease when bulk insert large amount of data only.
    – Akina
    Nov 1, 2018 at 12:15
  • @Akina - Even with a bulk insert, the speed is 10x faster because of eliminating various overheads of a single statement. My rule of thumb when bulk-inserting: do it in batches of 100-1000 rows.
    – Rick James
    Nov 2, 2018 at 4:50
  • @RickJames I speak about bulk insert rate difference when additional index cannot be used for insert optimization (otherwise the compare make no sense...). In that case each additional index unconditionally decreases insert speed. Especially in case when execution plan builder erroneously decide to use this index whereas its usage is not optimal. And I do not speak about bulk insert optimizing itself.
    – Akina
    Nov 2, 2018 at 5:18

2 Answers 2

Reset to default
5

Do you have to immediately index those columns even before you notice performance issues?

Short answer - Yes.

Where you have joins, you should also have Primary and Foreign Key Constraints and most DBMSs will insist that each of these is supported by an Index (even if it has to create one for you).

The vast majority of traffic in most databases is SELECTs, so putting off the creation of Indexes until performance becomes a problem is just setting yourself up to fail. You know you're going to need them, so save yourself the grief and put them in to start with.
(And, anyway, it won't be you that notices the "Performance Problem" - it will be your unhappy Users).

Improve this answer
1
  • 1
    I want to add that emergency indexing when performance is already poor can cause the service unavailability for a while. In the opposite if index is created along with the empty table the load is evenly spreaded over the table's lifetime. The little overhead for index actualizing is an acceptable price for the overall performance.
    – Kondybas
    Nov 1, 2018 at 14:13
1

A small amount of prudent planning by inserting relevant indexes on select queries that matter. Yes, you'll get a benefit, 20ms vs 80ms you may not notice but that could be a benefit. What you will notice is the CPU/IO resources to do the function of the MySQL server will be lower.

Unless you have significant insert/updates/deletes, don't worry about the modification speed of indexes, but still only add the columns needed.

Use explain to ensure your index is used. Understand how mysql uses indexes and principles of query optimization. When you have a small dataset is a good time to get skills creating the right index as its usually pretty quick to add/remove indexes for experimentation.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.