Re: Foreign keys and being FIRST index
http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. Major performance hit would be a guess. Otherwise the database would have to do a table scan. Think about how it would find the related record. I think is a requirement of db2 as well William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - 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: Foreign keys and being FIRST index
On 5 Mar 2003, at 19:39, Daevid Vincent wrote: Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. I think you're misunderstanding something. In the subject line you talk about the first index, and talking about using up the first spot seems to go with that misconception. The documentation you quote says nothing about such a restriction, however. It only says the key must be the first *column* in *an* index, which makes sense (otherwise the index wouldn't be useful for that key alone). [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. -- Dan Nelson [EMAIL PROTECTED] - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. - 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: Foreign keys and being FIRST index
In the last episode (Mar 05), Daevid Vincent said: I'm not sure that sentence means what you think it does. What they're saying is you need to index both fields, and if you decide to make that index a compound one with multiple keyparts, the foreign/referenced field must be the first. They don't have to be the first fields in the table or anything. Right, but sometimes it isn't, or you already used up that 'first' spot for a different foreign key reference in another table. Maybe I If it isn't, then create another index, on just that field. If you have multiple foreign keys, create a separate index, one for each foreign key. You can have multiple indexes on one table with no problems. Maybe MySQL should be modified so that the required indexes are silently created when a FOREIGN KEY clause is processed. am still not understanding something elementary, but I've tried to create some tables, and certain ones work, while others don't, and they're always related to that foreign key issue. In any event, it shouldn't matter if it's first or not! I should be able to use any/all fields in a any number of databases as foreign keys reguardless of their position in the schema or index. As for why you want them indexed. Have you ever tried deleting a lot of records from a table with a foreign key constraint on another table with no index? Each delete of your first table requires a full table scan of the second table, to make sure you're not violating the constraint. A co-worker forgot to index a constraint in Oracle once and his table updates took 2 hours instead of 2 minutes. I don't dispute the benefit of indexes, I just don't think it should be a REQUIREMENT. And to be honest, sometimes a table isn't made of millions of records, maybe it's only a few, but you still want ref integrity. If it's only a few records then the index won't take up that much space :) I'm pretty sure the requirement was done to make the InnoDB coders' lives easier. I know there are low-level functions for look up a keyvalue in an index. I don't know if there is one for do a full table scan and search for a value in one field without having to generate a small query and execute it (i.e. subquery, which mysql does not yet support). -- Dan Nelson [EMAIL PROTECTED] - 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
