1

I have log-type table in MySQL. There are indexes on 3 columns, because when doing some statistics out of that table, it obviously speeds up those statistic queries.

However, beeing a log-type table, where there is a lot of inserts but selects are very rare, it would make sense to disable reindexing the table with each insert. Is there a way how to tell MySQL not to automatically reindex the table and just leave indexes outdated and let them reindex them on-demand by us (somehow) ?

The only way right know which comes to my mind is to just create indexes before we run statistics queries and when those are done, just delete indexes. Or is there a better way ?

Frodik
  • 14,986
  • 23
  • 90
  • 141
  • 1
    You are setting up a situation where the indices cannot be used. This implies that you should not even have indices. – Tim Biegeleisen Jul 31 '22 at 11:32
  • Does this answer your question? [How to disable index in innodb](https://stackoverflow.com/questions/9524938/how-to-disable-index-in-innodb) – KIKO Software Jul 31 '22 at 11:38

1 Answers1

0

Creating an whole index for one query and then dropping it would be a waste of time. Creating the index would take at least as long as running the query without the help of an index.

By analogy, suppose you need to go to the store for some groceries, but it takes too long to walk there. So you walk further to the car dealership, buy a car, drive to the grocery store, then return the car. You could have just walked to the store in less time!

Besides, MySQL doesn't rebuild the whole index every time you insert. It only updates the existing index with a new value. Also, MySQL's storage engine is optimized to defer index updates and group them together for efficiency. You can read https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html for details on that feature.

Before you decide on any optimization, you should measure to make sure the optimization is needed. I understand that inserting to a table with no indexes is slightly quicker than a table with indexes, but is that difference crucial in your situation? Is the insert fast enough to keep up with the traffic when you have indexes? You might be trying to solve a problem needlessly.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828