So sometimes when I do an SQL query I find that it takes maybe 60 seconds to do one without proper indices but like 5 seconds to create an index, run the query and delete the index.
My question is... is there a technical reason why MySQL doesn't do this?
On a huge database (terabytes+), it may take a long time to create the index and delete it.
Also it may not guess the right way to create the index and actually make the query slower.
If you want this you need to create indexes yourself because DB do not know what needs to be indexed. So if it would increase your database performance you can index it, however if you indexing in the way that you need delete indexes after query, it is something misdesigned. But if it really helps you can do that.
However, execution time of different queries depending on database size variates. So you may run such situation, what your database grows very much and your way with indexing and removing indexes becomes slower then the usual way.
What is more, if you remove indexes after query, that probably means that those indexes would make other queries work slower and different queries require different indexing, so you could run into some kind of race situation. You will get some queries at the same time and all of them interfere to every other, all in all result will be worse then in usual way.
All in all, before taking some globally not applying solutions you have to make lots kind of tests, and those test would be as similar into real usage as possible. It means you have to create as much data as possible, try to simulate as much logged on users as possible. And just after this kind of testing decide if solution is worthy to accept or better to use some globally known way.
