INNODB automatically creates an index on foreign key fields. Is there a storage engine that allows me to disable that?

My googling on xtradb doesn't give me positive results. Perhaps, it is possible in Mariadb/Drizzle? Any ideas?

  • 1
    Ughh...That seems a bad question on the second read(more like it belongs on the mailing list of those projects)....on 3 comments agreeing will delete the question.. – Nandhini Anand Jeyahar May 4 '11 at 9:41

I don't think this is a bad question, but I do think that disabling indexes on foreign key fields is a bad idea. There is a very good reason why they are there. I have done a fair bit of DB admin work over time, and I have yet to see a situation where not having such an index would help the database perform better.

  • Infact, i can't imagine it as a good idea in terms of DB performance. The only reason, i wanted to ask was, we are exposing a UI link field that sets off foreign key creation. I was just wondering, if we can disable it during the peak usage hours, so that it will reduce the load. The other option of course is to postpone foreign key creation altogether. – Nandhini Anand Jeyahar May 4 '11 at 18:59
  • @Anand, once the initial index has been created, the performance gained by the index should be dramatically more than that lost during the creation of the index entry. If you have performance issues due to the index then you have serious problems elsewhere. – John Gardeniers May 5 '11 at 1:03
  • @John: True, however, i can't get any more information, as i don't work there any longer. I will accept wolfgangsz answer and consider this closed.Infact, even this problem i came across on the mailing list while proposing to remove foreign keys. – Nandhini Anand Jeyahar May 5 '11 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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