Natale, ----- Original Message ----- From: "Natale Babbo" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Wednesday, January 15, 2003 9:33 AM Subject: innofb foreign keys problem
> # --------- 3rd post ------------- # > # --------- PLEASE HELP ---------- # > > hi to all, > > is it still true that mysql/innodb needs explicit > index creation on foreign keys? yes. If you do not have a suitable index, foreign key checks will become very slow because they have to do a table scan. In the future, MySQL might do the following: in CREATE TABLE abbaguu ( ... FOREIGN KEY (column1) REFERENCES frobboz (column2) ) TYPE=InnoDB; it could check if there is a suitable index in abbaguu. If not, it would create the index automatically. But you are still left with the problem that also the table frobboz must have a suitable index. And I think it is not a good idea to run an implicit ALTER TABLE frobboz CREATE INDEX (column2). Users will be surprised that a simple CREATE TABLE can take hours. Another solution is to drop the requirement of indexes on the foreign key and on the referenced key. But then users will be surprised that a simple INSERT or DELETE can take 15 minutes. Conclusion: for now, it is best to design your database schema bearing in mind that adequate performance requires an index on the foreign key and on the referenced key. This holds for all database brands. Someone could volunteer to write a conversion tool which adds appropriate indexes to a set of CREATE TABLE statements where foreign keys are declared. ... > how can i create the database without creating > explicitly an index on each foreign keys of my > database? > > any suggestions are appreciated. > thanks to all. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
