title: Is adding and dropping indexes everyday on huge tables a good practice?
body: <p>I'm building a Web Application that is connected to a MySQL database.
  I've got two huge tables containing each about 40 millions rows at the moment, and they are receiving new rows everyday (which adds ~ 500 000-1000 000 rows everyday).</p>
  <p>The process to add new rows runs during the night, while no one can use the application, and the new rows' content depends on the result of some basic <code>SELECT</code> queries on the current database.
  In order to get the result of those <code>SELECT</code> statement fast enough, I'm using simple indexes (one column per index) on each column that appears at least once in a <code>WHERE</code> clause.</p>
  <p>The thing is, during the day, some totally different queries are run against those tables, including some &quot;range WHERE clause&quot; (<code>SELECT * FROM t1 WHERE a = a1 AND b = b1 AND (date BETWEEN d1 AND d2)</code>).
  I found on stack this very helpful mini-cookbook that advises you on which INDEXes you should use depending on how the database is queried: <a href="http://mysql.rjweb.org/doc.php/index_cookbook_mysql" rel="nofollow noreferrer">http://mysql.rjweb.org/doc.php/index_cookbook_mysql</a>
  They advice to use compound index: in my example query above it would give INDEX(a, b, date).</p>
  <p>It indeed increased the speed of the queries run during the day (from 1 minute to 8 seconds so I was truly happy).</p>
  <p>However, with those compound indexes, the required time to add new rows during the night totally explode (it would take more than one day to add the daily content).</p>
  <p>Here is my question: would that be ok to drop all the indexes every night, add the new content, and set back up the daily indexes?
  Or would that be dangerous since indexes are not meant to be rebuilt every day, especially on such big tables?
  I know such an operation would take approximately two hours in total (drop and recreate INDEXes).</p>
  <p>I am aware of the existence of <code>ALTER TABLE table_name DISABLE KEYS;</code> but I'm using InnoDB and I believe it is not made to work on InnoDB table.</p>