Tony Lee Tony Lee - 10 days ago 5
MySQL Question

add table index before SELECT WHERE and drop them before INSERT

Is it considered a best practice or an overall performance boost for MySQL if I add an index to my table before

SELECT..WHERE
statements and drop them before
INSERT
statements?

Answer

Even if the answer is obvious: Adding an index to a table before SELECT ... WHERE statements and dropping them before INSERT statements is really bad practice. And I'm pretty sure this holds true for every database.

You would have the overhead of creating an index entry for every row of the table on each SELECT. That's far more costly than creating/updating an index entry once when a new row is inserted or updated.