up vote 1 down vote favorite
share [g+] share [fb]

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?

link|improve this question
83% accept rate
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.. – Anand Jeyahar May 4 '11 at 9:41
feedback

1 Answer

up vote 0 down vote accepted

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.

link|improve this answer
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. – 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. – Anand Jeyahar May 5 '11 at 6:19
feedback

Your Answer

 
or
required, but never shown
discard

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