Re: innofb foreign keys problem
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
Re: innofb foreign keys problem
On Tue, 14 Jan 2003 16:41:06 +0100 (CET) Natale Babbo [EMAIL PROTECTED] wrote: # - 3rd post - # # - PLEASE HELP -- # hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? Yes why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? You can't -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - 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
Re: innofb foreign keys problem
many thanks for your reply. then i ask me: why mysql needs explicit creation instead of create itself what it needs? manually creating the index seems to be a big complication (for big databases) and a waste of time. don't you think so? it's a bug or a wanted feature? why? Thanks. --- Okan CIMEN [EMAIL PROTECTED] ha scritto: There are no other way you have to create the index first - Original Message - From: Natale Babbo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 5:41 PM 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? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) 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. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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 __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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
RE: innofb foreign keys problem
I agree with you. If there is no index already on the referenced fields then add one. By adding a foreign key you are already altering the table why not just finish the job. -Original Message- From: Natale Babbo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 8:58 AM To: Okan CIMEN; [EMAIL PROTECTED] Subject: Re: innofb foreign keys problem many thanks for your reply. then i ask me: why mysql needs explicit creation instead of create itself what it needs? manually creating the index seems to be a big complication (for big databases) and a waste of time. don't you think so? it's a bug or a wanted feature? why? Thanks. --- Okan CIMEN [EMAIL PROTECTED] ha scritto: There are no other way you have to create the index first - Original Message - From: Natale Babbo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 14, 2003 5:41 PM 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? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) 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. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.h tml - 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 __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.h tml - 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 - 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
Re: innofb foreign keys problem
then i ask me: why mysql needs explicit creation instead of create itself what it needs? manually creating the index seems to be a big complication (for big databases) and a waste of time. don't you think so? it's a bug or a wanted feature? why? I agree with you 100%. A foreign key,as its name implies, should create automatically a key. I would it is a wanted feature. __ / \\ @ ____@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, Presidente / \\ // / \\/ // // / //cel: +58 416 609-6213 /___ / _/\__\\//__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - 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
Re: innofb foreign keys problem
yes -- you need to create indexes on foreign key colums. it would be better if one of the following 2 things happened instead. 1. foreign key creation failed if no index existed. 2. indexes were created automatically it's more dangerous than actually not having foreign keys as it stands since it's easy to *think* your app is working correctly when it could in fact be trashing referential integrity. From: Natale Babbo [EMAIL PROTECTED] Subject: innofb foreign keys problem Date: 15/01/2003 2:41:06 To: [EMAIL PROTECTED] # - 3rd post - # # - PLEASE HELP -- # hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) 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. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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 This message was sent through MyMail http://www.mymail.com.au - 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
