Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
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

1 Answer 1

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.

share|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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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