Re: InnoDB interaction between secondary and primary keys.
Thank you Rick! -- Jeremy Chase http://twitter.com/jeremychase On Wed, Jan 30, 2013 at 4:24 PM, Rick James wrote: > The hint of a change is in here (search for "secondary"): > > http://jorgenloland.blogspot.co.uk/2012/12/favorite-mysql-56-features-optimizer.html > > > -Original Message- > > From: Rick James > > Sent: Wednesday, January 30, 2013 1:08 PM > > To: 'Jeremy Chase'; mysql@lists.mysql.com > > Subject: RE: InnoDB interaction between secondary and primary keys. > > > > secondarykey and redundantkey are redundant with each other -- in all > > versions of InnoDB. > > > > One "expert" said that redundant key would have two copies of `1`,`2`. > > I think he is wrong. I believe the two are the same in size. > > > > There is a subtle change in 5.6 that _may_ make a _few_ queries work > > better with redundantkey. > > > > I prefer to specify as many fields in the key as make sense for the > > SELECT(s), then let the engine add any more fields as needed to fill > > out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or > > (3,4,2), etc, depending on the queries. > > > > When looking up a row(s) by a secondary key, the engine first drills > > down the secondary BTree, finds the PK(s), then drills down the PRIMARY > > BTree. > > > > > -Original Message- > > > From: Jeremy Chase [mailto:jeremych...@gmail.com] > > > Sent: Wednesday, January 30, 2013 11:25 AM > > > To: mysql@lists.mysql.com > > > Subject: InnoDB interaction between secondary and primary keys. > > > > > > Hello, > > > > > > I've been working with a secondary index and would like some > > > clarification about how the primary columns are included. So, in the > > > following example, is the secondaryKey effectively the same as > > > redundantKey? > > > > > > CREATE TABLE `example` ( > > > `1` int(10) unsigned NOT NULL, > > > `2` int(10) unsigned NOT NULL, > > > `3` int(10) unsigned NOT NULL, > > > `4` int(10) unsigned NOT NULL, > > > PRIMARY KEY (`1`,`2`), > > > KEY `secondaryKey` (`3`, `4`) > > > KEY `redundantKey` (`3`, `4`, `1`, `2`) > > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > > > Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- > > > types.html > > > > > > "In InnoDB, each record in a secondary index contains the primary key > > > columns for the row, as well as the columns specified for the > > > secondary index." > > > > > > Thank you! > > > Jeremy >
RE: InnoDB interaction between secondary and primary keys.
The hint of a change is in here (search for "secondary"): http://jorgenloland.blogspot.co.uk/2012/12/favorite-mysql-56-features-optimizer.html > -Original Message- > From: Rick James > Sent: Wednesday, January 30, 2013 1:08 PM > To: 'Jeremy Chase'; mysql@lists.mysql.com > Subject: RE: InnoDB interaction between secondary and primary keys. > > secondarykey and redundantkey are redundant with each other -- in all > versions of InnoDB. > > One "expert" said that redundant key would have two copies of `1`,`2`. > I think he is wrong. I believe the two are the same in size. > > There is a subtle change in 5.6 that _may_ make a _few_ queries work > better with redundantkey. > > I prefer to specify as many fields in the key as make sense for the > SELECT(s), then let the engine add any more fields as needed to fill > out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or > (3,4,2), etc, depending on the queries. > > When looking up a row(s) by a secondary key, the engine first drills > down the secondary BTree, finds the PK(s), then drills down the PRIMARY > BTree. > > > -Original Message- > > From: Jeremy Chase [mailto:jeremych...@gmail.com] > > Sent: Wednesday, January 30, 2013 11:25 AM > > To: mysql@lists.mysql.com > > Subject: InnoDB interaction between secondary and primary keys. > > > > Hello, > > > > I've been working with a secondary index and would like some > > clarification about how the primary columns are included. So, in the > > following example, is the secondaryKey effectively the same as > > redundantKey? > > > > CREATE TABLE `example` ( > > `1` int(10) unsigned NOT NULL, > > `2` int(10) unsigned NOT NULL, > > `3` int(10) unsigned NOT NULL, > > `4` int(10) unsigned NOT NULL, > > PRIMARY KEY (`1`,`2`), > > KEY `secondaryKey` (`3`, `4`) > > KEY `redundantKey` (`3`, `4`, `1`, `2`) > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > > > Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- > > types.html > > > > "In InnoDB, each record in a secondary index contains the primary key > > columns for the row, as well as the columns specified for the > > secondary index." > > > > Thank you! > > Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: InnoDB interaction between secondary and primary keys.
secondarykey and redundantkey are redundant with each other -- in all versions of InnoDB. One "expert" said that redundant key would have two copies of `1`,`2`. I think he is wrong. I believe the two are the same in size. There is a subtle change in 5.6 that _may_ make a _few_ queries work better with redundantkey. I prefer to specify as many fields in the key as make sense for the SELECT(s), then let the engine add any more fields as needed to fill out the PK. That is, I might say (3,4) or (3,4,1) or (3,4,1,2) or (3,4,2), etc, depending on the queries. When looking up a row(s) by a secondary key, the engine first drills down the secondary BTree, finds the PK(s), then drills down the PRIMARY BTree. > -Original Message- > From: Jeremy Chase [mailto:jeremych...@gmail.com] > Sent: Wednesday, January 30, 2013 11:25 AM > To: mysql@lists.mysql.com > Subject: InnoDB interaction between secondary and primary keys. > > Hello, > > I've been working with a secondary index and would like some > clarification about how the primary columns are included. So, in the > following example, is the secondaryKey effectively the same as > redundantKey? > > CREATE TABLE `example` ( > `1` int(10) unsigned NOT NULL, > `2` int(10) unsigned NOT NULL, > `3` int(10) unsigned NOT NULL, > `4` int(10) unsigned NOT NULL, > PRIMARY KEY (`1`,`2`), > KEY `secondaryKey` (`3`, `4`) > KEY `redundantKey` (`3`, `4`, `1`, `2`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > Background: http://dev.mysql.com/doc/refman/5.5/en/innodb-index- > types.html > > "In InnoDB, each record in a secondary index contains the primary key > columns for the row, as well as the columns specified for the secondary > index." > > Thank you! > Jeremy
Re: Two Primary Keys
This isn't true for innodb. I think the only requirement is that you need to have a unique index on the auto increment column. We created a composite primary key + auto_increment to take advantage of clustering by primary key while satisfying unique constraint for the primary key. It worked out well for us except for the sheer size of the indexes. Kyong 2010/6/29 João Cândido de Souza Neto : > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > > > João Cândido. > > "Victor Subervi" escreveu na mensagem > news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... >> Hi; >> I have the following: >> >> create table pics ( >>  picture_id int auto_increment primary key, >>  product_sku int not null primary key, >>  picture_num int not null, >>  picture_desc varchar(100), >>  picture_data longblob >> ); >> >> which doesn't work I need to auto_increment and declare primary key on two >> fields. How do? >> TIA, >> Victor >> > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:   http://lists.mysql.com/mysql?unsub=kykim...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Two Primary Keys
@Dušan Pavlica; I must say thank you. This is really interesting, and in the 7 years I've been using mysql and sql, I've never know this (or had, but didn't realize what I had done). This little bit of information could make for some interesting changes to a couple of my projects I am working on, where I've done this, but done it in code, rather than in mysql. Steven Staples > -Original Message- > From: Dušan Pavlica [mailto:pavl...@unidataz.cz] > Sent: June 29, 2010 11:26 AM > To: Victor Subervi > Cc: mysql@lists.mysql.com > Subject: Re: Two Primary Keys > > Hi, > > try this and you will see exactly how autoincrement behaves in MyISAM > tables when it is part of primary key. > > 1) declare table like this: > CREATE TABLE `test_tbl` ( > `field1` int(10) unsigned NOT NULL default '0', > `field2` int(10) unsigned NOT NULL auto_increment, > `field3` char(10) NOT NULL default '', > PRIMARY KEY (`field1`,`field2`) > ) ENGINE=MyISAM; > > 2) then insert some values > INSERT INTO test_tbl (field1, field3) > VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); > > 3) see what's in the table > SELECT * FROM test_tbl ORDER BY field1; > > result is: > 1, 1, 'test1' > 1, 2, 'test3' > 2, 1, 'test2' > 2, 2, 'test4' > > field2 is unique only in context of field1. > > Hth, > Dusan > > > > Victor Subervi napsal(a): > > 2010/6/29 João Cândido de Souza Neto > > > > > >> As far as I know, if you have an auto_increment primary key, you cant > have > >> any other field in its primary key. > >> > >> > > > > Makes sense. Actually, I was just copying what someone else gave me and > > adding the auto_increment, then I got to wondering, what is the purpose > of > > having two primary keys? > > TIA, > > V > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.830 / Virus Database: 271.1.1/2917 - Release Date: 06/29/10 > 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
You were talking about multiple fields in the primary key, not multiple primary keys. On 06/29/2010 03:51 PM, Johan De Meersman wrote: Correct, but you still can't have more than one primary key. Kind of defeats the idea of it being primary, really. On Tue, Jun 29, 2010 at 3:36 PM, petya mailto:pe...@petya.org.hu>> wrote: If you use innodb, primary key lookups are far faster than secondary indexes. Peter On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote: I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
Hi, try this and you will see exactly how autoincrement behaves in MyISAM tables when it is part of primary key. 1) declare table like this: CREATE TABLE `test_tbl` ( `field1` int(10) unsigned NOT NULL default '0', `field2` int(10) unsigned NOT NULL auto_increment, `field3` char(10) NOT NULL default '', PRIMARY KEY (`field1`,`field2`) ) ENGINE=MyISAM; 2) then insert some values INSERT INTO test_tbl (field1, field3) VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); 3) see what's in the table SELECT * FROM test_tbl ORDER BY field1; result is: 1, 1, 'test1' 1, 2, 'test3' 2, 1, 'test2' 2, 2, 'test4' field2 is unique only in context of field1. Hth, Dusan Victor Subervi napsal(a): 2010/6/29 João Cândido de Souza Neto As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto > I think the best, or may be the right way is to use picture_id as primary > key and a unique index to product_sku. > Yes, sounds good. So the purpose, then, is to speed lookups on fields commonly accessed. I'd forgotten that. Thanks, V
Re: Two Primary Keys
I think the best, or may be the right way is to use picture_id as primary key and a unique index to product_sku. -- João Cândido de Souza Neto "Victor Subervi" escreveu na mensagem news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... > Hi; > I have the following: > > create table pics ( > picture_id int auto_increment primary key, > product_sku int not null primary key, > picture_num int not null, > picture_desc varchar(100), > picture_data longblob > ); > > which doesn't work I need to auto_increment and declare primary key on two > fields. How do? > TIA, > Victor > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
On 6/29/2010 9:24 AM, Victor Subervi wrote: Hi; I have the following: create table pics ( picture_id int auto_increment primary key, product_sku int not null primary key, picture_num int not null, picture_desc varchar(100), picture_data longblob ); which doesn't work I need to auto_increment and declare primary key on two fields. How do? TIA, Victor Use the other syntax for defining keys: create table pics ( picture_id int auto_increment, product_sku int not null, picture_num int not null, picture_desc varchar(100), picture_data longblob, PRIMARY KEY(product_sku_int, picture_id) ); -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
If you use innodb, primary key lookups are far faster than secondary indexes. Peter On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote: I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- João Cândido de Souza Neto "Victor Subervi" escreveu na mensagem news:aanlktinyaaps4jmbbjald6kdok7lfhxlykwq0tmpt...@mail.gmail.com... 2010/6/29 João Cândido de Souza Neto > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V
Re: Two Primary Keys
As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. João Cândido. "Victor Subervi" escreveu na mensagem news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... > Hi; > I have the following: > > create table pics ( > picture_id int auto_increment primary key, > product_sku int not null primary key, > picture_num int not null, > picture_desc varchar(100), > picture_data longblob > ); > > which doesn't work I need to auto_increment and declare primary key on two > fields. How do? > TIA, > Victor > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Two Primary Keys
Hi; I have the following: create table pics ( picture_id int auto_increment primary key, product_sku int not null primary key, picture_num int not null, picture_desc varchar(100), picture_data longblob ); which doesn't work I need to auto_increment and declare primary key on two fields. How do? TIA, Victor
Re: multiple primary keys on one table?
right... - Original Message - From: "Gabriel PREDA" <[EMAIL PROTECTED]> To: "Ferindo Middleton" <[EMAIL PROTECTED]> Cc: "Dan Buettner" <[EMAIL PROTECTED]>; Sent: Friday, October 06, 2006 6:31 PM Subject: Re: multiple primary keys on one table? You will have to UPDATE to NULL those fields, modify the aplication to enter NULL instead of empty-string... Then add the UNIQUE INDEX... In MySQL (unless modified) the dafault is that NULL values are incomparable thus allowing the creation of a UNIQUE INDEX. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
You will have to UPDATE to NULL those fields, modify the aplication to enter NULL instead of empty-string... Then add the UNIQUE INDEX... In MySQL (unless modified) the dafault is that NULL values are incomparable thus allowing the creation of a UNIQUE INDEX. -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
> I have a primary key set on a table which consists of the combination of the > values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this > so the records in this table do not have duplicates, being that no one > record should have the exact same name and schedule_id identifier. > > However, I want to keep this same restriction while also ensuring that no > two records have the same email_address and schedule_id identifier... > > You can't have the db enforce two different primary keys on one table, so > how would I implement having this kind of restriction, which, in itself, > seems to require that I have a second primary key to enforce another > constraint to dissalow records to be added that carry the same combination > of: email_address and schedule_id? You cannot have multiple PRIMARY key constraints, that's why it's called "primary". You can, however, use multiple "unique constraints", which do (almost) the same. With regard to other replies: indices are used for quick data retrieval, constraints for business requirements. That there happens to be something as a "unique index" is an implementation artifact. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
Thanks. I now have this issue where I can't create the unique index on this table because the email_address is often times blank or unknown for a while (The front-end_application sets is value to '' (blank) on inserts and updates if the user doesn't enter it so instead of going in as NULL, the field gets a blank field value... Is there a way to get UNIQUE index to treat blank data fields that are part of the index as null and not enforce the constraint if the subject columns are blank (whitespace)... Do I need to force the application to not pas the value into the field and make it NULL instead. just ferindo On 10/5/06, Dan Buettner <[EMAIL PROTECTED]> wrote: Ferindo, you can create multiple UNIQUE indexes on a table to enforce your data requirements. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Dan On 10/5/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: > I have a primary key set on a table which consists of the combination of the > values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this > so the records in this table do not have duplicates, being that no one > record should have the exact same name and schedule_id identifier. > > However, I want to keep this same restriction while also ensuring that no > two records have the same email_address and schedule_id identifier... > > You can't have the db enforce two different primary keys on one table, so > how would I implement having this kind of restriction, which, in itself, > seems to require that I have a second primary key to enforce another > constraint to dissalow records to be added that carry the same combination > of: email_address and schedule_id? > > just ferindo > >
Re: multiple primary keys on one table?
At 06:26 PM 10/5/2006, Ferindo Middleton wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo unique index M. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.13/463 - Release Date: 10/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple primary keys on one table?
Ferindo, you can create multiple UNIQUE indexes on a table to enforce your data requirements. http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Dan On 10/5/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple primary keys on one table?
I have a primary key set on a table which consists of the combination of the values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this so the records in this table do not have duplicates, being that no one record should have the exact same name and schedule_id identifier. However, I want to keep this same restriction while also ensuring that no two records have the same email_address and schedule_id identifier... You can't have the db enforce two different primary keys on one table, so how would I implement having this kind of restriction, which, in itself, seems to require that I have a second primary key to enforce another constraint to dissalow records to be added that carry the same combination of: email_address and schedule_id? just ferindo
Re: Problem with subselect and primary keys
Derek, I was able to replicate all the behaviors you describe in 5.0.21. I noticed you have a signed INT in one table and an UNsigned INT in the other. I changed t1 to UNsigned and then the query returns the results you would expect: +---+ | course_id | +---+ |-2 | |-1 | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | |68 | +---+ seems like the signed/unsigned data is not being converted before comparison, perhaps. If you can't change your column type in the table, perhaps you could use the CAST function in your queries? HTH, Dan On 9/29/06, Derek Fountain <[EMAIL PROTECTED]> wrote: Can someone tell me what's wrong with this test: create table t1 ( course_id int(10) signed not null, primary key (course_id) ); create table t2 ( course_id int(10) unsigned not null, primary key (course_id) ); insert into t1 values (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68); insert into t2 values (65),(66),(67); select distinct course_id from t1 where course_id not in (select course_id from t2); drop table t1; drop table t2; Running on 4.1.13 on SUSE Linux, this doesn't print anything. It should print those values in t1 but not t2. If I replace the subselect with the result of the subselect (65,66,67) then it works as expected. It also works if I remove the primary key from t2. If I just remove the primary key from t1 it prints a somewhat mysterious '1'. Can anyone explain what's going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with subselect and primary keys
Can someone tell me what's wrong with this test: create table t1 ( course_id int(10) signed not null, primary key (course_id) ); create table t2 ( course_id int(10) unsigned not null, primary key (course_id) ); insert into t1 values (1),(-1),(-2),(2),(3),(4),(5),(6),(7),(8),(65),(66),(67),(68); insert into t2 values (65),(66),(67); select distinct course_id from t1 where course_id not in (select course_id from t2); drop table t1; drop table t2; Running on 4.1.13 on SUSE Linux, this doesn't print anything. It should print those values in t1 but not t2. If I replace the subselect with the result of the subselect (65,66,67) then it works as expected. It also works if I remove the primary key from t2. If I just remove the primary key from t1 it prints a somewhat mysterious '1'. Can anyone explain what's going on? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
MySQL will not use the primary key unless you use the left most columns. For a 1 column primary key then it's easy. For a 2 column primary key you can either use the 1st column in the primary key or both columns. If you only reference the 2nd column the query will not use the primary key and will do a full table scan. In your case you are referencing "classb" which is not the left most collumn in the primary key set. Put the word "explain" preceding the statement and execute the query. it will show you what keys are used in the query and in your case it is none. You either need to define another KEY with classb as the 1st column in the definition or if all of your queries at least reference classb then you could rebuild the primary key and put classb as the 1st entry in the definition. - Original Message - From: "nngau" <[EMAIL PROTECTED]> To: "'Kishore Jalleda'" <[EMAIL PROTECTED]> Cc: Sent: Thursday, April 27, 2006 9:11 AM Subject: RE: Multiple primary keys Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb="216") order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau <[EMAIL PROTECTED]> wrote: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple primary keys
Thanks all. The query I run is a subquery. I noticed joined query run a lot faster than the sub. This is the subquery: select * from class_c where detail_id in (select classC from item_classification where classb="216") order by detail; This query takes nearly 3 minutes, before it did not take that long. I guess I should use a primary key As an index. I want to be able to add items that I can classify into different classa, classb or classc. Example: Itemid 1025 ClassA: 101 classB: 218 classC: 356 Same item can be put into another class. Itemid 105 ClassA: 101 classb: 218 classC: 357 So not having a primary key/index will slow my queries? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 10:49 AM To: nngau Cc: mysql@lists.mysql.com Subject: Re: Multiple primary keys On 4/27/06, nngau <[EMAIL PROTECTED]> wrote: > > Can someone figure out what's going on. This is the only change I made > to this table. Basically I don't want any duplicate rows, so I setup 4 > fields to be my primary key. > > When I do a simple select query it takes nearly 30 seconds to complete. > This is affecting my websites and taking a very long time to query the > Products. Have I setup this table right? Thank You!! > > +-+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +-+-+--+-+-+---+ > | itemID | int(6) | | PRI | 0 | | > | classA | int(3) | | PRI | 0 | | > | classB | int(3) | | PRI | 0 | | > | classC | int(3) | | PRI | 0 | | > | picture | varchar(10) | YES | | NULL| | > | sex | char(2) | YES | | NULL| | > +-+-+--+-+-+---+ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple primary keys
On 4/27/06, nngau <[EMAIL PROTECTED]> wrote: > > Can someone figure out what's going on. This is the only change > I made to this table. Basically I don't want any duplicate rows, so > I setup 4 fields to be my primary key. > > When I do a simple select query it takes nearly 30 seconds to complete. > This is affecting my websites and taking a very long time to query the > Products. Have I setup this table right? Thank You!! > > +-+-+--+-+-+---+ > | Field | Type| Null | Key | Default | Extra | > +-+-+--+-+-+---+ > | itemID | int(6) | | PRI | 0 | | > | classA | int(3) | | PRI | 0 | | > | classB | int(3) | | PRI | 0 | | > | classC | int(3) | | PRI | 0 | | > | picture | varchar(10) | YES | | NULL| | > | sex | char(2) | YES | | NULL| | > +-+-+--+-+-+---+ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > You can avoid duplicate rows with only one primary key, unless you have a specific reason for having your primary key span on four columns. Your query being slower depends on how you have indexed your columns relating to your queries. Please give us a sample query which is running slowly.. Kishore Jalleda http://kjalleda.googlepages.com/projects
Re: Multiple primary keys
Quoting nngau <[EMAIL PROTECTED]>: Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. If you don't want any duplicate rows, use UNIQUE, all those primary keys will just take up unwanted space. If you still think these fields should be indexed due to the large number of hits, then use an INDEX. Chris White -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple primary keys
Can someone figure out what's going on. This is the only change I made to this table. Basically I don't want any duplicate rows, so I setup 4 fields to be my primary key. When I do a simple select query it takes nearly 30 seconds to complete. This is affecting my websites and taking a very long time to query the Products. Have I setup this table right? Thank You!! +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | itemID | int(6) | | PRI | 0 | | | classA | int(3) | | PRI | 0 | | | classB | int(3) | | PRI | 0 | | | classC | int(3) | | PRI | 0 | | | picture | varchar(10) | YES | | NULL| | | sex | char(2) | YES | | NULL| | +-+-+--+-+-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table with multiple primary keys - How
Thank you. I went with your solution and the create table worked. Tested inserting on primary key and it worked. Need to load more data before I can test alt indexes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 3:39 AM To: mysql@lists.mysql.com Subject: Re: Table with multiple primary keys - How It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith > create table members ( > logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > email_addr varchar(30), ??? > last_name varchar(30), ??? > member_type char(1), > email_verified char(1), > logon_pwvarchar(15), > date_added date, > last_login timestamp, > first_name varchar(30), > addr1 varchar(30), > addr2 varchar(30), > cityvarchar(20), > state varchar(20), > zip varchar(15), > phone_home varchar(15), > phone_officevarchar(15), > phone_cell varchar(15), > mothers_maiden_name varchar(30), > ip_of_useratsignup varchar(16), > primary key(login_id, email_addr, last_name) > ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table with multiple primary keys - How
It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith > create table members ( > logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > email_addr varchar(30), ??? > last_name varchar(30), ??? > member_type char(1), > email_verified char(1), > logon_pwvarchar(15), > date_added date, > last_login timestamp, > first_name varchar(30), > addr1 varchar(30), > addr2 varchar(30), > cityvarchar(20), > state varchar(20), > zip varchar(15), > phone_home varchar(15), > phone_officevarchar(15), > phone_cell varchar(15), > mothers_maiden_name varchar(30), > ip_of_useratsignup varchar(16), > primary key(login_id, email_addr, last_name) > ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table with multiple primary keys - How
- Original Message - From: "fbsd_user" <[EMAIL PROTECTED]> To: Sent: Tuesday, March 07, 2006 11:40 PM Subject: Table with multiple primary keys - How What I am trying to do here is have 3 separate primary keys. Creating a mysql select on either of the Logon_id, email_addr, or last_name fields will do a single read to the matching value. Like having 3 different indexes into the same table. I don't want those 3 field concatenated together as a single key. Is this table definition correct? By definition, a table cannot have more than one primary key. Period. Your definition will probably execute successfully - I haven't tried it so I can't be sure - but 'successful' only means that it will (probably) not fail on a syntax error. Your definition does NOT create three primary keys, it creates one key on the combination of three values. In other words, your definition concatenates the three keys together, which you say you don't want to do. Your basic goal of having three separate primary keys is not possible. However, you _could_ create a primary key and two unique keys or three unique keys. But before you start doing that, I suggest you take a step back and rethink your design. The proper way to design databases is to do logical design FIRST, then consider physical design. In other words, make sure that your design hangs together logically first and meets all your business requirements. Then, and only then, should you consider physical design, i.e. redesigning tables to optimize performance. Logical design is always done with the assumption that you are working on a perfect processor that has outstanding performance no matter what query you run. Once the logical design is perfect, you can start being realistic and modify your design to optimize performance for real world non-perfect processors. I think you're leaping into physical design before you've finished logical design. You're worried about query performance before you've satisfied yourself that the logical design even works. You need to consider what primary key will support your logical design. Is the login_id alone sufficient to uniquely identify rows of the members table? Or do you need other columns to accomplish this? If, in fact, you need all three columns (login_id, email_addr, and last_name) to uniquely identify rows in the members table, then all three of those need to be in your single, concatenated key, exactly as you have it in your code. Is it possible for more than one person to have the login_id ABC123? If not, the login_id by itself should suffice to be your primary key. In that case, you may want to make email_addr and last_name separate unique keys - or not. If the login_id is sufficient to uniquely identify a member that's fine; make login_id your primary key. But you may not want to make email_addr or last_name unique keys. What if two of your members have separate login_ids but share an email address? Making email_addr unique will prevent one of your two members from being added to the table. This is an even bigger problem for the last_name; if you make it unique, and you already have one Smith in the table, you will never be allowed to have another Smith in the table! You need to sort out the issue of the primary key FIRST. Once that is resolved, you can start to think about making other keys unique or not. But right now, I think you're getting badly ahead of yourself. create table members ( logon_idvarchar(15), email_addr varchar(30), last_name varchar(30), member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, first_name varchar(30), addr1 varchar(30), addr2 varchar(30), cityvarchar(20), state varchar(20), zip varchar(15), phone_home varchar(15), phone_officevarchar(15), phone_cell varchar(15), mothers_maiden_name varchar(30), ip_of_useratsignup varchar(16), primary key(login_id, email_addr, last_name) ); -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 06/03/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table with multiple primary keys - How
In the last episode (Mar 07), fbsd_user said: > What I am trying to do here is have 3 separate primary keys. Creating > a mysql select on either of the Logon_id, email_addr, or last_name > fields will do a single read to the matching value. Like having 3 > different indexes into the same table. Create one primary key and two unique indexes. As far as mysql is concerned, a primary is just another unique index. I'd make login_id the real primary key since it should never change, unlike email_addr or last_name. Are you sure you want last_name to be unique? I think that one should be just a regular index. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table with multiple primary keys - How
What I am trying to do here is have 3 separate primary keys. Creating a mysql select on either of the Logon_id, email_addr, or last_name fields will do a single read to the matching value. Like having 3 different indexes into the same table. I don't want those 3 field concatenated together as a single key. Is this table definition correct? create table members ( logon_idvarchar(15), email_addr varchar(30), last_name varchar(30), member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, first_name varchar(30), addr1 varchar(30), addr2 varchar(30), cityvarchar(20), state varchar(20), zip varchar(15), phone_home varchar(15), phone_officevarchar(15), phone_cell varchar(15), mothers_maiden_name varchar(30), ip_of_useratsignup varchar(16), primary key(login_id, email_addr, last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are primary keys essential?
- Original Message - From: "James Harvard" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 21, 2005 9:08 PM Subject: Re: Are primary keys essential? Thanks the on & off-list replies, but I obviously didn't explain my situation very well! My app is essentially creating summary reports from large amounts of data. It is _not_ doing the actual data warehousing. It's international trade data. The data tables contain foreign keys for stuff like destination country, trade commodity category etc., but they are _not_ themselves referenced by any other table. Therefore I have not yet found, nor do I envisage finding, any use for an arbitrary auto_increment primary key. So why would/might I need a PK at all? Well, if this is _just_ summary data, I suppose it's okay to get along without PKs. But I've never had much to do with the theory and practice of data warehouses beyond a few very basic concepts so I wouldn't take that to the bank Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.2/208 - Release Date: 20/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
> > Shawn, I'm not quite clear what you are saying in your second last > paragraph. When you have this situation: > > ID (autogenerated) PART_NOPART_DESCRIPTION > 1 A01 Widget > 2 B03Grapple Grommet > 3 A02Snow Shovel > 4 D11Whisk > 5 C04Duct Tape > > Do you put the PK on ID alone, PART_NO alone, or the concatentation of ID > and PART_NO? I _think_ you mean that the PK is on PART_NO alone and that ID > is simply defined unique so that it can be the target for FKs that refer to > it but I want to be sure I'm not misreading you > > Rhino > > You figured me out. There would be a UNIQUE on ID and the PK on just PART_NO. With InnoDB, the PK is also a clustering index so it has the effect of storing data in PART_NO order. Since in a table like this you probably search on PART_NO quite frequently that works out just fine as an additional optimization. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Are primary keys essential?
Hi James, If the tables you use have primary keys that _YOU_ don't need or use then, for you, they are not essential. I do have a question, though. You are working with SQL tables, aren't you? And from wherever they came from or exist, I presume from your reply that you don't need the PKs. The only help I can envisage for you is if you want to look at the tables in PK order for some reason. Does your app create tables from the "large amouns of data?" If you report from the tables your app creates then you have no need at all for the PKs. Depends how you want the report data to appear. On the other hand, the "large amounts of data" db may need or want those PKs. Hope this makes sense, Ken - Original Message - From: "James Harvard" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 21, 2005 8:08 PM Subject: Re: Are primary keys essential? Thanks the on & off-list replies, but I obviously didn't explain my situation very well! My app is essentially creating summary reports from large amounts of data. It is _not_ doing the actual data warehousing. It's international trade data. The data tables contain foreign keys for stuff like destination country, trade commodity category etc., but they are _not_ themselves referenced by any other table. Therefore I have not yet found, nor do I envisage finding, any use for an arbitrary auto_increment primary key. So why would/might I need a PK at all? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are primary keys essential?
Hi James, Not AFAIK, one can create tables without specifying a PK and there is no objection. Data is stored quite happily and you should be able to use your FK's to access other data. It must be voluntary because the ALTER TABLE DML statement has DROP PRIMARY KEY drops the primary index. Note: In older versions of MySQL, if no primary index existed, then DROP PRIMARY KEY would drop the first UNIQUE index in the table. This is not the case in MySQL 5.0, where trying to use DROP PRIMARY KEY on a table with no primary key will give rise to an error. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 December 2005 12:45 PM To: mysql@lists.mysql.com Subject: Re: Are primary keys essential? In hindsight my thread title was misleading - sorry. Should have been "are primary keys _always_ essential?". JH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are primary keys essential?
In hindsight my thread title was misleading - sorry. Should have been "are primary keys _always_ essential?". JH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are primary keys essential?
Thanks the on & off-list replies, but I obviously didn't explain my situation very well! My app is essentially creating summary reports from large amounts of data. It is _not_ doing the actual data warehousing. It's international trade data. The data tables contain foreign keys for stuff like destination country, trade commodity category etc., but they are _not_ themselves referenced by any other table. Therefore I have not yet found, nor do I envisage finding, any use for an arbitrary auto_increment primary key. So why would/might I need a PK at all? TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Are primary keys essential?
Hi James, An internal, unique, auto-increment PK is a good idea on dynamic, large tables. Smaller, mostly static, tables can often do without a PK. In a backend DB it's a godsend. What would happen if I depended on the programming in the client-side code to handle the PK?? It would be impossible. Example: Customers need a PK. An internal, auto-increment, unique, integer works best on DBs. They are optimized for it, especially SQL tables. Example: Counties. These names hardly ever change and are rather small. So, you could do without the internal, integer PK. BUT-- suppose every invoice must show the county? Then I would need a county (e.g., varchar 25) field for the county name of Yoknapatawphahootchee. If the customer has thousands of invoices with us then county starts to take up space. And a fair amount of it. Millions of invoices would take up 21 millions of bytes of storage. Just multiply number of customers by numbers of invoices. If the invoice uses a 1 byte field for the county with an FK in the county file we automatically save 40 millions of bytes of storage. Since no state has more than 255 counties, a tiny int will work. Example: States. Hmmm. Let's see, abbreviation for Minnesota is MN. Only two bytes needed there. OK, That will work for a PK. We'll do without the integer, auto-increment, key here. And we will violate consistency in the DB. No big deal in this case. I'll go either way here. Another good reason is YOU control the PK. Social Security numbers and ZipCodes are already being prepared for changes. Why go bonkers 7 years from now? And who wants to back-code and back-fill all that code & data? Truncated, damaged file? What invoice does the row point to? What is the date? If it's the last row in the PK then that's where the fixing begins by date and by PK#. Since a PK guarantees uniqueness, I can breathe easily if the file gets clobbered. Example: Someone with BIG access rights accidentally deletes invoices from 1996. But they should have done it for 1995. (We keep 10 years history on hand in an archive file.) Easy to fix. What's the last# in 1995? The first # in 1997? Restore only those rows with those numbers, inclusive. Suppose now the Customer file is by name, ZIP and first 4 digits in the address? (Or something like that. Many of my junk mail addresses show "WAGNEKO64054A". If another Wagner, Kenneth O. arrives in zip code 64054, presumably, he becomes "WAGNEKO64054B." No middle initial? The it's "WAGNEK_64054.") How could I know which customers to restore without going thru a lot of queries? What if I have to break up a table because it's getting too large? Has too many columns or rows added to it in the last year? The integer PK works really well. And it is small and simple to eyeball. And I can still have any other unique index I want on columns in the new related 2nd table. Mostly, I like the int, auto-increment, unique, unsigned PK because it's used everywhere, always looks the same and is very, very fast because the indexes are small, optimized for SQL usage and very parsimonious about RAM. Intuitively, the numbers also tell me about size and activity levels. Best of all, they are inviolate. I can trust them. Well, OK, maybe once a decade a gamma ray hits the oxide layer on the disk drive and changes 3,212,434,334 to 3,712,434,334. Still easy to fix. Drop the key and re-create it. It will automatically show up. Either as a gap or a duplicate. But it WILL show up as something. Bottom line, uniqueness, stability and order are the "sine qua non"* of good data organization. (*Means without which nothing.) HTH, Ken - Original Message - From: "James Harvard" <[EMAIL PROTECTED]> To: Sent: Wednesday, December 21, 2005 6:01 PM Subject: Are primary keys essential? The PK thread has reminded me of a question I had but never resolved when designing the table structure of the big data warehouse app I was droning on about just now in the aforementioned thread. As need to import some hundreds of millions of rows in the next week, I think now would be a good idea to get a definite answer! The core of the app is a mass of data, broken into many tables that I normally only need to query individually. Because I felt uneasy not including a primary key and need to get a proof-of-concept db running I ended up putting an auto_increment int column in the data tables. (Yes, I know, an extra 4 bytes per row when I was talking about saving every byte possible in my last post. ) But the PK column is never used either as a foreign key or in app code for the table itself. But I couldn't put a PK on a combination of other columns, because I don't think I can be sure of uniqueness. Can I just drop the PK column? BTW I'm sure this is addressed in all those good books on database d
Are primary keys essential?
The PK thread has reminded me of a question I had but never resolved when designing the table structure of the big data warehouse app I was droning on about just now in the aforementioned thread. As need to import some hundreds of millions of rows in the next week, I think now would be a good idea to get a definite answer! The core of the app is a mass of data, broken into many tables that I normally only need to query individually. Because I felt uneasy not including a primary key and need to get a proof-of-concept db running I ended up putting an auto_increment int column in the data tables. (Yes, I know, an extra 4 bytes per row when I was talking about saving every byte possible in my last post. ) But the PK column is never used either as a foreign key or in app code for the table itself. But I couldn't put a PK on a combination of other columns, because I don't think I can be sure of uniqueness. Can I just drop the PK column? BTW I'm sure this is addressed in all those good books on database design and theory I should have, but never have, read. But I'm a bit short of time, and it's quicker just to pick the brains of you folks! Quicker for me, that is - sorry! TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
Auto-incremented integers (be it bigint, mediumint, etc) are, from a purist point of view, better than "natural" primary keys, like part number etc. Read Practical Issues in Database Management, by Fabian Pascal. He argues against natural primary keys, because the business rules that underly the data could change. The example you use is the social security number - say the US federal government decides that a change is required. Say families will all have the same SSN number, so that they may file joint income tax returns. You have no control over it. Also, in order to guarantee uniqueness, you often have to use a composite primary key (a primary key made up of many columns). This adds complexity and size to your database. It also makes using tools like Hibernate (an database <--> java-objects mapping tool that is very popular and powerful) more difficult to use (at least in version 2.1), because composite primary keys have extra overhead. If a part-number and manufacturer (or supplier) are a unique combination, create a unique index on the two. Keep your primary key abstract (and unnatural). I know there are alot of "what-if" statements (I mean, how often will the SSN change?), and maybe you've never heard of Hibernate let alone had any desire to use it, but the underlying argument is valid - business rules change. For complex schemas, with lots of data, that could be a nightmare if some external data source that you have no control over suddenly changes the rules underlying their data. Timestamps (last-modified-dates) are very useful for trying to track down problems. David Kenneth Wagner wrote: Rhino, What I do is put the ID (integer Primary Key, auto-increment, unique) first. First key in every file. Then define my indexes. You could, do it the other way as you ask. But, I found this way is consistent. It can be traced anywhere on any file. Timestamp on important or critical files is also a big plus. It's not needed on static or almost static files like counties, states, departments, etc. But orders, parts, inventory, customers and so on benefit from a timestamp. Beside SQL is optimized for it AFAIK. This hasn't ever been an issue aside from a few people asking about how to use the ID? Or what's it for? Ken - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Kenneth Wagner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: "mysql" Sent: Wednesday, December 21, 2005 4:57 PM Subject: Re: Reason for Auto-increment primary keys? - Original Message - From: <[EMAIL PROTECTED]> To: "Kenneth Wagner" <[EMAIL PROTECTED]> Cc: "mysql" ; "Rhino" <[EMAIL PROTECTED]> Sent: Wednesday, December 21, 2005 5:15 PM Subject: Re: Reason for Auto-increment primary keys? "Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005 04:27:53 PM: Hi Rhino, Excellent question. Felt as you do, initially. Here's what changed my mind. Integer keys are fast. And small. Hence, they take very little RAM space. They are contiguous. A missing PK is easy to find. There's a gap in the number sequence. Can't do this with the part description. No way to tell if a record is missing. Example: The system gets hung up or crashes and a reboot is needed. How to test the integrity of the parts table. I.e., anything missing? Check the PK for continuity is a good place to start. With a timestamp I would even know the date where the file got truncated. Example. It's Dec 20th. The highest date in the file is Dec 1st at rec# 1203023. That's where the analysis would begin. Other files that didn't get truncated but have the related key # in them would tip me off as to how much is missing. Like an order file. Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. Activity testing: Let's say I do some statistical testing. Like how many new parts per month on average. Easy to do with the integer PK. Even easier if it has a timestamp. Then if the average suddenly drops or increases I would want to know why. Or modify my DB tables or coding. Note that the timestamp does not have to be in your example table. It could be in an insert/update table that just tracks what has been added or updated by PK, timestamp, activity type and updatedbyuserID. So, there's 2 cents worth. Wondering how relevant this is? HTH, Ken Wagner - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, December 21, 2005 2:54 PM Subject: Reason for Auto-increment primary keys? > One technique that I see a lot on this mailing list is people putting > auto-incremented integer pr
Re: Reason for Auto-increment primary keys?
I admit I too am in the habit of always defining an auto_increment primary key, but recently gathered my courage and omitted it from a match-up table joining a table of users to a table of categories they were allowed to use - an auto-generated primary key would have been completely redundant. It felt quite daring though! :o Seriously though, in my experience at least, one is usually trying to represent something 'real world' in a database schema, and usually there just isn't something that clearly fits the requirements for a primary key (well defined in the quote Rhino gave). So very often one just has to create an 'artificial' pk and then it makes sense to use one which will be as efficient as possible. In an app I'm just finishing I split the core data into many tables, but still one of those is 40 million rows and growing fast. So I shifted everything I could out into related tables to try and reduce the column sizes. By my calculations every byte of storage requirement I can knock off each row means 40 MB for that table's data file alone, and probably nearly 1 GB (more with index files?) over the whole app. That's got to have a significant effect on performance. For example, the date (year-month) is thus shoe-horned into a tinyint. This means that in about 12 years the client will have to think about bumping that column to smallint, but by then I suspect the performance implications of that will be insignificant. Heh, 12 years ago I woudn't have worried about shaving 40 MB off a 2 GB file, because 40 MB _was_ my hard disc! So presumably as the years go by increasing processor power and storage speed & size will mean we will no longer have to compromise on purity of db design to get acceptable performance. OK, now I'm just rambling. James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
Rhino, What I do is put the ID (integer Primary Key, auto-increment, unique) first. First key in every file. Then define my indexes. You could, do it the other way as you ask. But, I found this way is consistent. It can be traced anywhere on any file. Timestamp on important or critical files is also a big plus. It's not needed on static or almost static files like counties, states, departments, etc. But orders, parts, inventory, customers and so on benefit from a timestamp. Beside SQL is optimized for it AFAIK. This hasn't ever been an issue aside from a few people asking about how to use the ID? Or what's it for? Ken - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "Kenneth Wagner" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: "mysql" Sent: Wednesday, December 21, 2005 4:57 PM Subject: Re: Reason for Auto-increment primary keys? - Original Message - From: <[EMAIL PROTECTED]> To: "Kenneth Wagner" <[EMAIL PROTECTED]> Cc: "mysql" ; "Rhino" <[EMAIL PROTECTED]> Sent: Wednesday, December 21, 2005 5:15 PM Subject: Re: Reason for Auto-increment primary keys? "Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005 04:27:53 PM: Hi Rhino, Excellent question. Felt as you do, initially. Here's what changed my mind. Integer keys are fast. And small. Hence, they take very little RAM space. They are contiguous. A missing PK is easy to find. There's a gap in the number sequence. Can't do this with the part description. No way to tell if a record is missing. Example: The system gets hung up or crashes and a reboot is needed. How to test the integrity of the parts table. I.e., anything missing? Check the PK for continuity is a good place to start. With a timestamp I would even know the date where the file got truncated. Example. It's Dec 20th. The highest date in the file is Dec 1st at rec# 1203023. That's where the analysis would begin. Other files that didn't get truncated but have the related key # in them would tip me off as to how much is missing. Like an order file. Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. Activity testing: Let's say I do some statistical testing. Like how many new parts per month on average. Easy to do with the integer PK. Even easier if it has a timestamp. Then if the average suddenly drops or increases I would want to know why. Or modify my DB tables or coding. Note that the timestamp does not have to be in your example table. It could be in an insert/update table that just tracks what has been added or updated by PK, timestamp, activity type and updatedbyuserID. So, there's 2 cents worth. Wondering how relevant this is? HTH, Ken Wagner - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, December 21, 2005 2:54 PM Subject: Reason for Auto-increment primary keys? > One technique that I see a lot on this mailing list is people putting > auto-incremented integer primary keys on their tables. > > Maybe I'm just "old school" but I've always thought that you should choose > a primary key based on data that is actually in the table whenever > possible, rather than generating a new value out of thin air. > > The only exception that comes to mind is things like ID numbers; for > example, it is better to use an internally-generated integer for an > employee number than it is to use an employee's name. Even the combination > of first name and last name is not necessarily unique - I could cite a > real life example -and, of course, people can change their names. That > makes names less desireable than a generated value when you are trying to > uniquely indentify such entities. In such a case, a nice, reasonable short > integer is easier. > > I just found this rather good definition of primary keys at > http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The > relevant bit says that a primary key must have: > - a non-null value for each instance of the entity > - a value that is unique for each instance of an entity > - a value that must not change or become null during the life of the each > instance of the entity > > That article makes the same basic remarks about name vs. ID but makes the > point that it is more commonly the case that table designers will use > something like a social security number - an _externally_ generated > number - to distinguish between employees rather than an > internally-generated number. > > But the trend in this mailing list is toward using generated values as > primary keys
Re: Reason for Auto-increment primary keys?
- Original Message - From: <[EMAIL PROTECTED]> To: "Kenneth Wagner" <[EMAIL PROTECTED]> Cc: "mysql" ; "Rhino" <[EMAIL PROTECTED]> Sent: Wednesday, December 21, 2005 5:15 PM Subject: Re: Reason for Auto-increment primary keys? "Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005 04:27:53 PM: Hi Rhino, Excellent question. Felt as you do, initially. Here's what changed my mind. Integer keys are fast. And small. Hence, they take very little RAM space. They are contiguous. A missing PK is easy to find. There's a gap in the number sequence. Can't do this with the part description. No way to tell if a record is missing. Example: The system gets hung up or crashes and a reboot is needed. How to test the integrity of the parts table. I.e., anything missing? Check the PK for continuity is a good place to start. With a timestamp I would even know the date where the file got truncated. Example. It's Dec 20th. The highest date in the file is Dec 1st at rec# 1203023. That's where the analysis would begin. Other files that didn't get truncated but have the related key # in them would tip me off as to how much is missing. Like an order file. Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. Activity testing: Let's say I do some statistical testing. Like how many new parts per month on average. Easy to do with the integer PK. Even easier if it has a timestamp. Then if the average suddenly drops or increases I would want to know why. Or modify my DB tables or coding. Note that the timestamp does not have to be in your example table. It could be in an insert/update table that just tracks what has been added or updated by PK, timestamp, activity type and updatedbyuserID. So, there's 2 cents worth. Wondering how relevant this is? HTH, Ken Wagner - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, December 21, 2005 2:54 PM Subject: Reason for Auto-increment primary keys? > One technique that I see a lot on this mailing list is people putting > auto-incremented integer primary keys on their tables. > > Maybe I'm just "old school" but I've always thought that you should choose > a primary key based on data that is actually in the table whenever > possible, rather than generating a new value out of thin air. > > The only exception that comes to mind is things like ID numbers; for > example, it is better to use an internally-generated integer for an > employee number than it is to use an employee's name. Even the combination > of first name and last name is not necessarily unique - I could cite a > real life example -and, of course, people can change their names. That > makes names less desireable than a generated value when you are trying to > uniquely indentify such entities. In such a case, a nice, reasonable short > integer is easier. > > I just found this rather good definition of primary keys at > http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The > relevant bit says that a primary key must have: > - a non-null value for each instance of the entity > - a value that is unique for each instance of an entity > - a value that must not change or become null during the life of the each > instance of the entity > > That article makes the same basic remarks about name vs. ID but makes the > point that it is more commonly the case that table designers will use > something like a social security number - an _externally_ generated > number - to distinguish between employees rather than an > internally-generated number. > > But the trend in this mailing list is toward using generated values as > primary keys in virtually EVERY table, even when good primary keys can be > found in the (non-generated) data already existing in the table. > > Now, I haven't done anything remotely resembling a quantified analysis so > maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of > table descriptions with auto-generated keys and I don't think they were > all a name vs. ID scenario > > Has anyone else noticed a similar trend? > > If this trend is real, it doesn't seem like a very good trend to me. For > example, if you were keeping track of parts in a warehouse, why would > anyone make a table that looked like this: > ID (autogenerated PK) PART_NOPART_DESCRIPTION > 1 A01 Widget > 2B03Grapple Grommet > 3A02
Re: Reason for Auto-increment primary keys?
Kenneth Wagner wrote: Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. This in my mind is one of the biggest reasons to use an AUTO_INCREMENT column as a primary key when other columns would work. If you have a table that will act as a parent in a parent/child relationship and you've identified a composite (more than one column) PK as: col1 VARCHAR(25) col2 VARCHAR(30) Then the child table would need to have a copy of both columns posted to setup a composite foriegn key: CREATE TABLE child ( child_id INT AUTO_INCREMENT, col1 VARCHAR(25) NOT NULL, col2 VARCHAR(30) NOT NULL, INDEX fk_ind (col1, col2), FOREIGN KEY (col1, col2) REFERENCES parent(col1,col2) ON DELETE... PRIMARY KEY (child_id) ) So not only are you making a more complex index on the parent table by using two character columns you are also posting two columns into the child table(s) whenever you want to use this as a parent table. And with MySQL you generally have to make another INDEX on the FK columns as well as shown above. (I've never understood why this isn't automatic) In this case you have to decide whether or not it's good to maintain the uniqueness constraint on the parent table columns if you add an AUTO_INCREMENT column by doing something like: CREATE TABLE parent ( parent_id INT AUTO_INCREMENT, col1 VARCHAR(25) NOT NULL, col2 VARCHAR(30) NOT NULL, some_other_col VARCHAR(200) NULL, UNIQUE (col1, col2), PRIMARY KEY (parent_id) ); The UNIQUE constraint will still create an index on the text columns so you will still need to consider space/performance issues but at least your child tables only need to post a copy of the INT column "parent_id". In my mind it's always good to use UNIQUE in these cases so your real primary keys are in your table structure to prevent getting bad data. My $0.02 Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
"Kenneth Wagner" <[EMAIL PROTECTED]> wrote on 12/21/2005 04:27:53 PM: > Hi Rhino, > > Excellent question. Felt as you do, initially. > > Here's what changed my mind. > > Integer keys are fast. And small. Hence, they take very little RAM space. > > They are contiguous. A missing PK is easy to find. There's a gap in the > number sequence. > Can't do this with the part description. No way to tell if a record is > missing. > > Example: The system gets hung up or crashes and a reboot is needed. > How to test the integrity of the parts table. I.e., anything missing? Check > the PK for > continuity is a good place to start. With a timestamp I would even know the > date > where the file got truncated. Example. It's Dec 20th. The highest date in > the file is > Dec 1st at rec# 1203023. That's where the analysis would begin. Other files > that > didn't get truncated but have the related key # in them would tip me off as > to how > much is missing. Like an order file. > > Speed. Especially where related files are concerned. Foreign keys. Links on > integer > fields are faster, smaller and more efficient. Keys remain smaller and > faster. > > Activity testing: Let's say I do some statistical testing. Like how many > new parts > per month on average. Easy to do with the integer PK. Even easier if it has > a timestamp. > Then if the average suddenly drops or increases I would want to know why. Or > modify > my DB tables or coding. Note that the timestamp does not have to be in your > example > table. It could be in an insert/update table that just tracks what has been > added or updated > by PK, timestamp, activity type and updatedbyuserID. > > So, there's 2 cents worth. > > Wondering how relevant this is? > > HTH, > > Ken Wagner > > > > - Original Message - > From: "Rhino" <[EMAIL PROTECTED]> > To: "mysql" > Sent: Wednesday, December 21, 2005 2:54 PM > Subject: Reason for Auto-increment primary keys? > > > > One technique that I see a lot on this mailing list is people putting > > auto-incremented integer primary keys on their tables. > > > > Maybe I'm just "old school" but I've always thought that you should choose > > a primary key based on data that is actually in the table whenever > > possible, rather than generating a new value out of thin air. > > > > The only exception that comes to mind is things like ID numbers; for > > example, it is better to use an internally-generated integer for an > > employee number than it is to use an employee's name. Even the combination > > of first name and last name is not necessarily unique - I could cite a > > real life example -and, of course, people can change their names. That > > makes names less desireable than a generated value when you are trying to > > uniquely indentify such entities. In such a case, a nice, reasonable short > > integer is easier. > > > > I just found this rather good definition of primary keys at > > http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The > > relevant bit says that a primary key must have: > > - a non-null value for each instance of the entity > > - a value that is unique for each instance of an entity > > - a value that must not change or become null during the life of the each > > instance of the entity > > > > That article makes the same basic remarks about name vs. ID but makes the > > point that it is more commonly the case that table designers will use > > something like a social security number - an _externally_ generated > > number - to distinguish between employees rather than an > > internally-generated number. > > > > But the trend in this mailing list is toward using generated values as > > primary keys in virtually EVERY table, even when good primary keys can be > > found in the (non-generated) data already existing in the table. > > > > Now, I haven't done anything remotely resembling a quantified analysis so > > maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of > > table descriptions with auto-generated keys and I don't think they were > > all a name vs. ID scenario > > > > Has anyone else noticed a similar trend? > > > > If this trend is real, it doesn't seem like a very good trend to me. For > > example, if you were keeping track of parts in a warehouse, why would > > anyone make a table that looked like this: > &
Re: Reason for Auto-increment primary keys?
If this trend is real, it doesn't seem like a very good trend to me. For example, if you were keeping track of parts in a warehouse, why would anyone make a table that looked like this: ID (autogenerated PK) PART_NOPART_DESCRIPTION 1 A01 Widget 2B03Grapple Grommet 3A02Snow Shovel 4D11Whisk 5C04Duct Tape when this table is simpler: PART_NO (PK) PART_DESCRIPTION A01 Widget B03Grapple Grommet A02Snow Shovel D11Whisk C04Duct Tape Would anyone care to convince me that the first version of the table is "better" than the second version in some way? I can go either way on this. I like the PART_NO as the pri key better, but I will quite often use the autogenerated pk to make my life easier. How? Take for instance when you get "the email": Hey Rhino, I have a spec change for the warehouse application you are working on. Can you plese modify the application so that "Widget" and "SuperWidget II plus" can have the same part number. I know it sounds weird, but the guys upstairs want it. I have tried to talk them out of it, but legal insists, and I can't do anything about it at this point, sorry. Oh, and we need this to QA by EOD Monday. Work as may hours as you need over the weekend to make this happen. thanks, Joe. Joe Blow Sr. Project Manager Thingey, inc. -- It is much easier to make these sorts of changes if your pk is something that you have control over rather than an soemthing that is defined by someone else because they will change the definition on you mid-way though the project. Rudolf. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
Hi Rhino, >Maybe I'm just "old school" but I've always thought that you should >choose a primary key based on data that is actually in the table >whenever possible, rather than generating a new value out of thin air. Mebbe every db list should drag this out for re-examination once a year or so :-) . >From the principle that the smaller the opportunity there is for violation of PK uniqueness, and from the fact that any real-world data, being empirical, has error bars, I conclude that in many cases the most robust PK is INT auto_increment. For a longer version of this argument (ie putting the cat amongst the pigeons) click on "Practical database design rules" at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. Happy holidays to one and all. PB - Rhino wrote: One technique that I see a lot on this mailing list is people putting auto-incremented integer primary keys on their tables. Maybe I'm just "old school" but I've always thought that you should choose a primary key based on data that is actually in the table whenever possible, rather than generating a new value out of thin air. The only exception that comes to mind is things like ID numbers; for example, it is better to use an internally-generated integer for an employee number than it is to use an employee's name. Even the combination of first name and last name is not necessarily unique - I could cite a real life example -and, of course, people can change their names. That makes names less desireable than a generated value when you are trying to uniquely indentify such entities. In such a case, a nice, reasonable short integer is easier. I just found this rather good definition of primary keys at http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The relevant bit says that a primary key must have: - a non-null value for each instance of the entity - a value that is unique for each instance of an entity - a value that must not change or become null during the life of the each instance of the entity That article makes the same basic remarks about name vs. ID but makes the point that it is more commonly the case that table designers will use something like a social security number - an _externally_ generated number - to distinguish between employees rather than an internally-generated number. But the trend in this mailing list is toward using generated values as primary keys in virtually EVERY table, even when good primary keys can be found in the (non-generated) data already existing in the table. Now, I haven't done anything remotely resembling a quantified analysis so maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of table descriptions with auto-generated keys and I don't think they were all a name vs. ID scenario Has anyone else noticed a similar trend? If this trend is real, it doesn't seem like a very good trend to me. For example, if you were keeping track of parts in a warehouse, why would anyone make a table that looked like this: ID (autogenerated PK) PART_NO PART_DESCRIPTION 1 A01 Widget 2 B03 Grapple Grommet 3 A02 Snow Shovel 4 D11 Whisk 5 C04 Duct Tape when this table is simpler: PART_NO (PK) PART_DESCRIPTION A01 Widget B03 Grapple Grommet A02 Snow Shovel D11 Whisk C04 Duct Tape Would anyone care to convince me that the first version of the table is "better" than the second version in some way? I just want to be sure that no one has come along with some new and compelling reason to autogenerate keys when perfectly good keys can be found within the data already. I don't mind being "old school" but I don't want to be "out to lunch" :-) Rhino No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.3/209 - Release Date: 12/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reason for Auto-increment primary keys?
Hi Rhino, Excellent question. Felt as you do, initially. Here's what changed my mind. Integer keys are fast. And small. Hence, they take very little RAM space. They are contiguous. A missing PK is easy to find. There's a gap in the number sequence. Can't do this with the part description. No way to tell if a record is missing. Example: The system gets hung up or crashes and a reboot is needed. How to test the integrity of the parts table. I.e., anything missing? Check the PK for continuity is a good place to start. With a timestamp I would even know the date where the file got truncated. Example. It's Dec 20th. The highest date in the file is Dec 1st at rec# 1203023. That's where the analysis would begin. Other files that didn't get truncated but have the related key # in them would tip me off as to how much is missing. Like an order file. Speed. Especially where related files are concerned. Foreign keys. Links on integer fields are faster, smaller and more efficient. Keys remain smaller and faster. Activity testing: Let's say I do some statistical testing. Like how many new parts per month on average. Easy to do with the integer PK. Even easier if it has a timestamp. Then if the average suddenly drops or increases I would want to know why. Or modify my DB tables or coding. Note that the timestamp does not have to be in your example table. It could be in an insert/update table that just tracks what has been added or updated by PK, timestamp, activity type and updatedbyuserID. So, there's 2 cents worth. Wondering how relevant this is? HTH, Ken Wagner - Original Message - From: "Rhino" <[EMAIL PROTECTED]> To: "mysql" Sent: Wednesday, December 21, 2005 2:54 PM Subject: Reason for Auto-increment primary keys? One technique that I see a lot on this mailing list is people putting auto-incremented integer primary keys on their tables. Maybe I'm just "old school" but I've always thought that you should choose a primary key based on data that is actually in the table whenever possible, rather than generating a new value out of thin air. The only exception that comes to mind is things like ID numbers; for example, it is better to use an internally-generated integer for an employee number than it is to use an employee's name. Even the combination of first name and last name is not necessarily unique - I could cite a real life example -and, of course, people can change their names. That makes names less desireable than a generated value when you are trying to uniquely indentify such entities. In such a case, a nice, reasonable short integer is easier. I just found this rather good definition of primary keys at http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The relevant bit says that a primary key must have: - a non-null value for each instance of the entity - a value that is unique for each instance of an entity - a value that must not change or become null during the life of the each instance of the entity That article makes the same basic remarks about name vs. ID but makes the point that it is more commonly the case that table designers will use something like a social security number - an _externally_ generated number - to distinguish between employees rather than an internally-generated number. But the trend in this mailing list is toward using generated values as primary keys in virtually EVERY table, even when good primary keys can be found in the (non-generated) data already existing in the table. Now, I haven't done anything remotely resembling a quantified analysis so maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of table descriptions with auto-generated keys and I don't think they were all a name vs. ID scenario Has anyone else noticed a similar trend? If this trend is real, it doesn't seem like a very good trend to me. For example, if you were keeping track of parts in a warehouse, why would anyone make a table that looked like this: ID (autogenerated PK) PART_NOPART_DESCRIPTION 1 A01 Widget 2B03Grapple Grommet 3A02Snow Shovel 4D11Whisk 5C04Duct Tape when this table is simpler: PART_NO (PK) PART_DESCRIPTION A01 Widget B03Grapple Grommet A02Snow Shovel D11Whisk C04Duct Tape Would anyone care to convince me that the first version of the table is "better" than the second version in some way? I just want to be sure that no one has come along with some new and compelling reason to autogenerate keys when perfectly good keys c
Reason for Auto-increment primary keys?
One technique that I see a lot on this mailing list is people putting auto-incremented integer primary keys on their tables. Maybe I'm just "old school" but I've always thought that you should choose a primary key based on data that is actually in the table whenever possible, rather than generating a new value out of thin air. The only exception that comes to mind is things like ID numbers; for example, it is better to use an internally-generated integer for an employee number than it is to use an employee's name. Even the combination of first name and last name is not necessarily unique - I could cite a real life example -and, of course, people can change their names. That makes names less desireable than a generated value when you are trying to uniquely indentify such entities. In such a case, a nice, reasonable short integer is easier. I just found this rather good definition of primary keys at http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The relevant bit says that a primary key must have: - a non-null value for each instance of the entity - a value that is unique for each instance of an entity - a value that must not change or become null during the life of the each instance of the entity That article makes the same basic remarks about name vs. ID but makes the point that it is more commonly the case that table designers will use something like a social security number - an _externally_ generated number - to distinguish between employees rather than an internally-generated number. But the trend in this mailing list is toward using generated values as primary keys in virtually EVERY table, even when good primary keys can be found in the (non-generated) data already existing in the table. Now, I haven't done anything remotely resembling a quantified analysis so maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of table descriptions with auto-generated keys and I don't think they were all a name vs. ID scenario Has anyone else noticed a similar trend? If this trend is real, it doesn't seem like a very good trend to me. For example, if you were keeping track of parts in a warehouse, why would anyone make a table that looked like this: ID (autogenerated PK) PART_NOPART_DESCRIPTION 1 A01 Widget 2B03Grapple Grommet 3A02Snow Shovel 4D11Whisk 5C04Duct Tape when this table is simpler: PART_NO (PK) PART_DESCRIPTION A01 Widget B03Grapple Grommet A02Snow Shovel D11Whisk C04Duct Tape Would anyone care to convince me that the first version of the table is "better" than the second version in some way? I just want to be sure that no one has come along with some new and compelling reason to autogenerate keys when perfectly good keys can be found within the data already. I don't mind being "old school" but I don't want to be "out to lunch" :-) Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.2/208 - Release Date: 20/12/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys in tables [restarted]
"Peter Normann" <[EMAIL PROTECTED]> wrote on 06/16/2005 02:15:34 PM:
> Sorry guys,
> I just learned that ctrl-return would send the email you are composing,
so I
> got cut off short, so let me try again :-/
> It appears to be usual practice from what I can see in this list to use
a
> primary key that actually contain somewhat meaningful data.
> I have always used primary keys solely for one purpose only: To identify
a
> table row uniquely, using auto incremental integers. Actually, if I were
to
> use a unique number in an application, I would add a column to the table
for
> just that. I would never use the primary key. The table may contain
foreign
> keys and those may hold an entirely different value than this primary -
even
> though the other table would share a one to one relationship.
> I have always felt, that if you would rely on a primary key for holding
> somewhat meaningful data, somewhere down the road it could spell
trouble.
> Anyway, I just wanted to hear if anybody would share their thoughts on
any
> advantages or drawbacks as to having the primary key contain meaningful
> data.
> Peter Normann
Primary Keys (PKs) have special significance in the realm of database
theory and operation. A PK value or tuple (for multi-column PKs) will
uniquely identify each and every row of data within a table. Because of
this unique distinction, many database engines (including MySQL's InnoDB
engine) will store their data in PK order (called clustering). Many (if
not most) engines use the PK as one half of the "pointers" table used to
store data in the actual files of the database. Because pointers are
usually stored as offsets (a fixed size) the size of the PK is strongly
influenced on the choice of the column(s) that construct the PK. If no PK
is declared for a table, some databases will use EVERY column (all of
them) and hash them together to make the pointers table. That means you
can have duplicate rows in your database and if you wanted to update just
one of those duplicates, you couldn't.
The reason that many of us use "real" data in our primary keys is because
the PK is also an index. It's two uses for the price of one: data
integrity, faster lookups.
However, it's more common to assign auto-inc numbers to rows so that it
becomes possible to refer to those rows with a numeric value rather than
to duplicate the actual "unique data".
Numbers take up either 2,4, or 8 bytes and are compared MUCH faster than
string values. Those facts form part of the basis of the theory of
database normalization. When you are trying to normalize a database, you
want to replace commonly used values with a reference of those values.
That way the values are stored only once (less space = faster searches).
However, it would still be "correct" (in a textbook-kind-of-way) to use
the full "unique data" tuple for referencing a row in one table from
another ,a foreign key(FK). Under most circumstances, tables are arranged
in a parent-child relationship where the parent can have several children
but each child can have only one parent. That means that the FK stored on
the child table must uniquely identify a single row of the parent table.
That leaves us with only two options: use the auto-inc value or use the
"unique data" tuple. It takes much less space (usually) to store just the
auto-inc value.
Using a numeric PK to store, sort, and lookup records will be faster than
using the data itself so most people declare their auto-inc columns as the
PK of their table and assign a UNIQUE index to their actual data. That way
their data values are both indexed and protected against duplication.
Would changing the "meaningful" data (the unique tuple) break a FK (if it
was the value used)? Maybe. In MySQL, you can declare FKs for cascading
updates. That means that if I had was using the tuple {STEVE,989984} for a
FK and STEVE wants to be called STEVEN then updating the PK to {STEVEN,
989984) would automatically update the FKs on the tables that used it. If
you didn't have cascading updates, and you wanted to change the value, you
would need to:
a) start a transaction
b) change all child tables to point to the new FK
c) change the PK on the parent table
d) commit the transaction
By wrapping the process within a transactional boundary, we prevent the
database from becoming "inconsistent" (changed in one place but not in
another). So while it's possible to use "real" data as FK tuples, it's
takes more administration if you need to change the PK value it
references. That is another argument in favor of the auto-inc value being
the PK. Because it has no significance to the data (except to identify a
row) changing significant fields will not break relational integrity.
I know I have rambled but you asked a very "wide" question and it's late
in my work day. Thanks for bearing with me.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Primary keys in tables [restarted]
Sorry guys, I just learned that ctrl-return would send the email you are composing, so I got cut off short, so let me try again :-/ It appears to be usual practice from what I can see in this list to use a primary key that actually contain somewhat meaningful data. I have always used primary keys solely for one purpose only: To identify a table row uniquely, using auto incremental integers. Actually, if I were to use a unique number in an application, I would add a column to the table for just that. I would never use the primary key. The table may contain foreign keys and those may hold an entirely different value than this primary - even though the other table would share a one to one relationship. I have always felt, that if you would rely on a primary key for holding somewhat meaningful data, somewhere down the road it could spell trouble. Anyway, I just wanted to hear if anybody would share their thoughts on any advantages or drawbacks as to having the primary key contain meaningful data. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary keys in tables
Hi everybody, It appears to be usual practice from what I can see in this list to use a primary key that actually contain somewhat meaningful data. I have always used primary keys solely for one purpose only: To identify a table row uniquely, using autoincremental integers. Actually, if I were to use a unique number in an application, I would add a column to the table for just that. I would never use the primary key. The table may contain foreign keys and those may hold an entirely different value than this primary - even though the other table would share a one to one relationship. I have always felt, that if you would rely on a primary key for holding somewhat meaningful data, somewhere down the road it could spell trouble. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
On Sunday 15 May 2005 20:31, Dan Bolser wrote: > You must mean a multipart primary key with three parts :) > > or "multiple-column indexes" > > That is what I would do (use a multiple-column index (primary key) - its > kinda based on opinion, but I think you should let the real data be the > primary key where appropriate, and avoid artificial 'auto_increment' > unless they are specifically useful or necessary in your situation. > > I.e build the database around the data, not the other way round :) > > That is just my design preference though. > > Not sure about performance problems, but you get two 'indexes' for free > with one multipart primary key with three parts (so the order of the > parts is significant (depending on your application)). > I would advocate quite the opposite. The data is the data: primary/foreign keys are data about the database. You should always separate the two. For instance, the foreign key values used in a junction table, used to manage many-to-many relationships, are _simply_ foreign keys; the need for their presence in that particular table has more to do with normalisation and good database design than anything about the actual data in the real world. By all means, build the database AROUND the data, but don't actually USE the data to build the database. You can never really guarantee the uniqueness (or availability) of the data that you select for your primary key when you use _real_ data. A classic example, is where someone is using National Insurance numbers for employees an Employee database as the primary key for each employee - what happens if you suddenly start hiring foreign contractors, where no such data exists? Do you start inventing false data, just to satisfy your need for a foreign key? If you'd used auto-increment fields, the problem wouldn't arise. Furthermore, auto_increments are just integers: there is very little overhead involved in handling them. Real data is usually either more complex, or is apt to become so at some point in the future. Daniel Walker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
On Sun, 15 May 2005, Martijn Tonies wrote:
>Bob,
>
>> I have a table, see below, that contains a single primary key
>(SubTestCaseKey ) and a number of foreign keys
>>
>> * plantriggers_ID_FK ,
>> * testcase_root_ID_FK
>> * testcasesuffix_name_FK
>>
>> What I want to ensure is that there are no duplicate records when
>considering the three foreign keys above. Would it be appropriate to remove
>the single primary and replace with three multiple primary keys? Is there a
>performance impact when doing this. this seems overly complex and wonder if
>I should be breaking the table up to simplify? Any suggestions would be
>appreciated.
>>
>
>There's no such thing as 3 primary keys.
You must mean a multipart primary key with three parts :)
or "multiple-column indexes"
That is what I would do (use a multiple-column index (primary key) - its
kinda based on opinion, but I think you should let the real data be the
primary key where appropriate, and avoid artificial 'auto_increment'
unless they are specifically useful or necessary in your situation.
I.e build the database around the data, not the other way round :)
That is just my design preference though.
Not sure about performance problems, but you get two 'indexes' for free
with one multipart primary key with three parts (so the order of the
parts is significant (depending on your application)).
...
http://dev.mysql.com/doc/mysql/en/multiple-column-indexes.html
>
>Why do you need "subtestcasekey"? If the foreign key columns should be
>unique, why not
>make those 3 columns the primary key?
>
>With regards,
>
>Martijn Tonies
>Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
>Server
>Upscene Productions
>http://www.upscene.com
>
>> Bob
>>
>> CREATE TABLE testplans (
>> SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
>> plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
>> testcase_root_ID_FK INTEGER NOT NULL,
>> testcasesuffix_name_FK VARCHAR(20) NULL,
>> FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
>> Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
>> DateMod TIMESTAMP NULL,
>> tester_list_Name_FK VARCHAR(50) NULL,
>> PRIMARY KEY(SubTestCaseKey),
>> INDEX testplans_FKIndex1(tester_list_Name_FK),
>> INDEX testplans_FKIndex2(testcasesuffix_name_FK),
>> INDEX testplans_FKIndex3(testcase_root_ID_FK),
>> INDEX testplans_FKIndex4(plantriggers_ID_FK),
>> FOREIGN KEY(tester_list_Name_FK)
>> REFERENCES tester_list(Name)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(testcasesuffix_name_FK)
>> REFERENCES testcasesuffix(name)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(testcase_root_ID_FK)
>> REFERENCES testcase_root(ID)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE,
>> FOREIGN KEY(plantriggers_ID_FK)
>> REFERENCES plantriggers(ID)
>> ON DELETE RESTRICT
>> ON UPDATE CASCADE
>> )
>> TYPE=InnoDB;
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single vs Multiple primary keys
Bob,
> I have a table, see below, that contains a single primary key
(SubTestCaseKey ) and a number of foreign keys
>
> * plantriggers_ID_FK ,
> * testcase_root_ID_FK
> * testcasesuffix_name_FK
>
> What I want to ensure is that there are no duplicate records when
considering the three foreign keys above. Would it be appropriate to remove
the single primary and replace with three multiple primary keys? Is there a
performance impact when doing this. this seems overly complex and wonder if
I should be breaking the table up to simplify? Any suggestions would be
appreciated.
>
There's no such thing as 3 primary keys.
Why do you need "subtestcasekey"? If the foreign key columns should be
unique, why not
make those 3 columns the primary key?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
> Bob
>
> CREATE TABLE testplans (
> SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
> plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
> testcase_root_ID_FK INTEGER NOT NULL,
> testcasesuffix_name_FK VARCHAR(20) NULL,
> FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
> Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
> DateMod TIMESTAMP NULL,
> tester_list_Name_FK VARCHAR(50) NULL,
> PRIMARY KEY(SubTestCaseKey),
> INDEX testplans_FKIndex1(tester_list_Name_FK),
> INDEX testplans_FKIndex2(testcasesuffix_name_FK),
> INDEX testplans_FKIndex3(testcase_root_ID_FK),
> INDEX testplans_FKIndex4(plantriggers_ID_FK),
> FOREIGN KEY(tester_list_Name_FK)
> REFERENCES tester_list(Name)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(testcasesuffix_name_FK)
> REFERENCES testcasesuffix(name)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(testcase_root_ID_FK)
> REFERENCES testcase_root(ID)
> ON DELETE RESTRICT
> ON UPDATE CASCADE,
> FOREIGN KEY(plantriggers_ID_FK)
> REFERENCES plantriggers(ID)
> ON DELETE RESTRICT
> ON UPDATE CASCADE
> )
> TYPE=InnoDB;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single vs Multiple primary keys
I have a table, see below, that contains a single primary key (SubTestCaseKey )
and a number of foreign keys
* plantriggers_ID_FK ,
* testcase_root_ID_FK
* testcasesuffix_name_FK
What I want to ensure is that there are no duplicate records when considering
the three foreign keys above. Would it be appropriate to remove the single
primary and replace with three multiple primary keys? Is there a performance
impact when doing this. this seems overly complex and wonder if I should be
breaking the table up to simplify? Any suggestions would be appreciated.
Bob
CREATE TABLE testplans (
SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
testcase_root_ID_FK INTEGER NOT NULL,
testcasesuffix_name_FK VARCHAR(20) NULL,
FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
DateMod TIMESTAMP NULL,
tester_list_Name_FK VARCHAR(50) NULL,
PRIMARY KEY(SubTestCaseKey),
INDEX testplans_FKIndex1(tester_list_Name_FK),
INDEX testplans_FKIndex2(testcasesuffix_name_FK),
INDEX testplans_FKIndex3(testcase_root_ID_FK),
INDEX testplans_FKIndex4(plantriggers_ID_FK),
FOREIGN KEY(tester_list_Name_FK)
REFERENCES tester_list(Name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcasesuffix_name_FK)
REFERENCES testcasesuffix(name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcase_root_ID_FK)
REFERENCES testcase_root(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(plantriggers_ID_FK)
REFERENCES plantriggers(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: <[EMAIL PROTECTED]>
Pgr: <[EMAIL PROTECTED]>
Re: Question about combination PRIMARY keys and INDEX
At 9:10 +1000 4/21/05, Daniel Kasak wrote: Daevid Vincent wrote: If I have a table with a primary key like this: CREATE TABLE `answers` ( `qid` INT UNSIGNED NOT NULL , `userid` INT UNSIGNED NOT NULL , `groupid` INT UNSIGNED NOT NULL , `comments` TEXT NOT NULL , PRIMARY ( `qid` , `userid` , `groupid` ) ); But I will also be searching in various pages, for all 'answers' from a certain userid or say that belong to a certain groupid, do I still need to create separate INDEXes for those fields, or is it enough to have them in that combination PRIMARY key? You need to have separate indexes. If you have a composite index and just have one field in the where clause ( or in a join ), the index won't be used at all. It says this somewhere in the documentation. Not quite. The index could be used if were searching for a column or columns that form a leftmost prefix of the index. That means it could be used if you were searching for qid values, or qid+userid values. But just groupid values are not leftmost prefixes of the index, so you do need a separate index for groupid. Also, while the above table definition is legal, I would have an auto_increment primary key, and then if you want to make ( `qid` , `userid` , `groupid` ) unique, define a unique index across them. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about combination PRIMARY keys and INDEX
Daevid Vincent wrote: >If I have a table with a primary key like this: > >CREATE TABLE `answers` ( >`qid` INT UNSIGNED NOT NULL , >`userid` INT UNSIGNED NOT NULL , >`groupid` INT UNSIGNED NOT NULL , >`comments` TEXT NOT NULL , > PRIMARY ( `qid` , `userid` , `groupid` ) >); > >But I will also be searching in various pages, for all 'answers' from a >certain userid or say that belong to a certain groupid, do I still need to >create separate INDEXes for those fields, or is it enough to have them in >that combination PRIMARY key? > > You need to have separate indexes. If you have a composite index and just have one field in the where clause ( or in a join ), the index won't be used at all. It says this somewhere in the documentation. Also, while the above table definition is legal, I would have an auto_increment primary key, and then if you want to make ( `qid` , `userid` , `groupid` ) unique, define a unique index across them. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about combination PRIMARY keys and INDEX
If I have a table with a primary key like this: CREATE TABLE `answers` ( `qid` INT UNSIGNED NOT NULL , `userid` INT UNSIGNED NOT NULL , `groupid` INT UNSIGNED NOT NULL , `comments` TEXT NOT NULL , PRIMARY ( `qid` , `userid` , `groupid` ) ); But I will also be searching in various pages, for all 'answers' from a certain userid or say that belong to a certain groupid, do I still need to create separate INDEXes for those fields, or is it enough to have them in that combination PRIMARY key? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary Keys, Multiple Index and Searching
Hello. > So in this case MySQL will choose to use the largest index that suits MySQL will choose index which returns less rows. > Given the high cardinality of `manufacturer` Cardinality - the number of unique values in the index. So manufacturer index usually has low cardinality and key on goods which they produce large cardinality. > Given the high cardinality of `manufacturer`, if an index was created on > just `manufacturer` as well, would MySQL use a sort of union on the > Primary Key index and `manufacturer` index to find all relevant rows? Or MySQL will only use a single index, and probaly it'll be the left part of the primary key. You should read: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html Jonathan Wright <[EMAIL PROTECTED]> wrote: > Gleb Paharenko wrote: >> Hello. >> >> If you have a separate indexes on section and status columns, MySQL >> will use only one of them. And as the column status has very small number >> of possible values the cardinality of separate index on it will be too low >> and optimizer won't use this index. I think, if slow updates won't be a >> problem, >> use multiple-column indexes as now. > > So in this case MySQL will choose to use the largest index that suits > the optimization of the query, and there isn't a problem with having > multiple indexes referencing similar column patterns? > > However, say for example that I had a similar structure, but for a shop > (ok, not a fantastic example!): > > > CREATE TABLE products ( > `store` INT, > `item_barcode` CHAR(x), > `manufacturer` VARCHAR(y), > PRIMARY KEY(`store`, `item_barcode`) > ); > > > And say I wanted to search for all products in a particular store > related to the one currently being viewed. In this case, I'd have a > SELECT with searches on `store` and `manufacturer`. > > Given the high cardinality of `manufacturer`, if an index was created on > just `manufacturer` as well, would MySQL use a sort of union on the > Primary Key index and `manufacturer` index to find all relevant rows? Or > would it still be better to create a multiple column index across > `store` and `manufacturer` together with the Primary Key? > > Sorry to bother you again, it's just something I've not come across (and > something not really been covered in what I've read/studied), and I'm > interested to know how the query is optimized given a multiple column > searches and possible multiple indexes. :) > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary Keys, Multiple Index and Searching
Gleb Paharenko wrote: Hello. If you have a separate indexes on section and status columns, MySQL will use only one of them. And as the column status has very small number of possible values the cardinality of separate index on it will be too low and optimizer won't use this index. I think, if slow updates won't be a problem, use multiple-column indexes as now. So in this case MySQL will choose to use the largest index that suits the optimization of the query, and there isn't a problem with having multiple indexes referencing similar column patterns? However, say for example that I had a similar structure, but for a shop (ok, not a fantastic example!): CREATE TABLE products ( `store` INT, `item_barcode` CHAR(x), `manufacturer` VARCHAR(y), PRIMARY KEY(`store`, `item_barcode`) ); And say I wanted to search for all products in a particular store related to the one currently being viewed. In this case, I'd have a SELECT with searches on `store` and `manufacturer`. Given the high cardinality of `manufacturer`, if an index was created on just `manufacturer` as well, would MySQL use a sort of union on the Primary Key index and `manufacturer` index to find all relevant rows? Or would it still be better to create a multiple column index across `store` and `manufacturer` together with the Primary Key? Sorry to bother you again, it's just something I've not come across (and something not really been covered in what I've read/studied), and I'm interested to know how the query is optimized given a multiple column searches and possible multiple indexes. :) -- Jonathan Wright Life has no meaning unless we can enjoy what we've been given -- Running on Gentoo Linux (2.6.10-gentoo-r7-djnauk-b03 i686 AMD Athlon(tm) XP 2100+ GNU/Linux) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary Keys, Multiple Index and Searching
Hello.
If you have a separate indexes on section and status columns, MySQL
will use only one of them. And as the column status has very small number
of possible values the cardinality of separate index on it will be too low
and optimizer won't use this index. I think, if slow updates won't be a problem,
use multiple-column indexes as now.
Jonathan Wright <[EMAIL PROTECTED]> wrote:
> Hiya,
>
> I've been trying to find out about this for a few days, but can't seam
> to find much information about it on the web.
>
> At the moment I've got a few tables, one of this looks like:
>
>
> CREATE TABLE `news` (
> `section` TINYINT UNSIGNED ZEROFILL NOT NULL,
> `article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
> `status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
>
>
> PRIMARY KEY (`section`, `article`),
> INDEX news_search (`section`, `status`),
> )
>
>
> There is another table with defines the sections of the site, and each
> section can have it's own articles. Hence the Primary Key. However, most
> of the searches are going to be with the status aswell, as I want to
> display articles marked with status='show', i.e.
>
>
> SELECT * FROM news WHERE section='x' AND status='show';
>
>
> At the moment, the indexes for section are being duplicated, which I
> suppose is a waste of space, and slows the updates (although that's not
> a problem, as updates aren't anywhere near as common as searches).
>
> However, would it be better to knock `section` out of news_search and
> have just `status`? Does MySQL (currently 4.0.14, although it'll be
> running on 3.23.53 eventually) allow the searching of multiple indexes,
> or is it better to specify multiple indexes with similar columns to
> maximize performance?
>
> Thanks,
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary Keys, Multiple Index and Searching
Hiya,
I've been trying to find out about this for a few days, but can't seam
to find much information about it on the web.
At the moment I've got a few tables, one of this looks like:
CREATE TABLE `news` (
`section` TINYINT UNSIGNED ZEROFILL NOT NULL,
`article` SMALLINT(4) NOT NULL AUTO_INCREMENT,
`status` ENUM('show', 'awaiting', 'hide') NOT NULL DEFAULT 'awaiting',
PRIMARY KEY (`section`, `article`),
INDEX news_search (`section`, `status`),
)
There is another table with defines the sections of the site, and each
section can have it's own articles. Hence the Primary Key. However, most
of the searches are going to be with the status aswell, as I want to
display articles marked with status='show', i.e.
SELECT * FROM news WHERE section='x' AND status='show';
At the moment, the indexes for section are being duplicated, which I
suppose is a waste of space, and slows the updates (although that's not
a problem, as updates aren't anywhere near as common as searches).
However, would it be better to knock `section` out of news_search and
have just `status`? Does MySQL (currently 4.0.14, although it'll be
running on 3.23.53 eventually) allow the searching of multiple indexes,
or is it better to specify multiple indexes with similar columns to
maximize performance?
Thanks,
--
jonathan wright
// mail at djnauk.co.uk // running on gentoo linux
// life has no meaning unless we can enjoy what we've been given
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table scan in join on primary keys??
I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why aren't my PRIMARY KEYs being used?
I disagree with your LEFT JOIN/RIGHT JOIN results.
SELECT
activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc
FROM activelayers
LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
lrsrc.lid=activelayers.lid
RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
RIGHT JOIN activenodes ON nrsrc.id=activenodes.id
ORDER BY activelayers.lid DESC;
Since you RIGHT JOINed "nrsrc" and "activenodes" to your query, neither
your 1st nor your 5th columns should contain any null values. So, the
columns that could contain null values are the 2nd, 3rd, and 4th (as both
"activelayers" and "lrsrc" are optional tables
However, only your 2nd and 4th columns contain nulls. So, where are the
optional (non-matching) rows for column 3? Where are those null values?
There should be nulls there for everywhere you have a null in columns 2
and 4, right?
...OR...
Are the tables "activelayers", "nrsrc", and "activenodes" participating in
a Cartesian product and only "lrsrc" is actually optional?
Which method of looking at this query is correct? Can you be certain that
the same style query will respond with the same decisions about which
tables are optional and which ones aren't (because of the mix of LEFT and
RIGHT joins in the same query)? The only way to avoid this kind of
"order-of-operations" dilemma is to use parentheses to group (nest) your
joins so that they evaluate in the correct order. However, last time I
checked, that is still on the TODO list to fix.
http://dev.mysql.com/doc/mysql/en/TODO_sometime.html (see third from last)
So to get back to the original issue.
To recap: from http://lists.mysql.com/mysql/174702
Conceptually, here's what I'm trying to do: I've got a set of tasks to
execute
("frames"). The frames which are ready to execute are in "wait" mode.
These
frames are associated with "layers" (in the table "layers"), and these
layers
have 0 or more "layer resource requirements" (in the table "lrsrc"). I
also
have a table of compute nodes ("nodes"). Each of these nodes has 0 or
more
"node resources" (in the table "nrsrc"). If a layer requires "linux" and
"perl" resources, frames in that layer will only run on compute nodes
which
have "linux" and "perl" resources.
What was the question are you trying to answer? I know it was something
about matching layers and nodes but I am not perfectly clear on what
results you wanted.
I really do want to help and I don't want to argue over what I think is a
bug (or an under-developed section) in the program. You may have received
the results you wanted this time but I am not confident that this query is
correct for the question you are asking nor am I confident that it will
continue to return correct results in the future.
Thanks for your patience,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/30/2004 04:21:15 AM:
>
> On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote:
>
> > I think it may be because of your mixed left and right joins. There
> > are several bugs listed that show that the optimizer mishandles
> > certain combinations of left and right joins.
> >
> > SELECT
> >
> >
activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr
> > src.rsrc)
> > as matchcount,activenodes.name,activenodes.rsrcc
> > FROM activelayers
> > LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
> > lrsrc.lid=activelayers.lid
> > INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
> > INNER JOIN activenodes ON nrsrc.id=activenodes.id
> > GROUP BY activelayers.id,activelayers.lid,activenodes.id
> > HAVING matchcount=activelayers.rsrcc
> > ORDER BY activelayers.lid DESC;
>
> This actually didn't produce the same result. I'm doing a RIGHT JOIN
> rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which
> do not match layer resources (lrsrc), or nodes with no layer resources
> at all. This example makes the difference a little clearer:
>
> SELECT
> activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc
> FROM activelayers
> LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND
> lrsrc.lid=activelayers.lid
> RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc
> RIGHT JOIN activenodes ON nrsrc.id=activenodes.id
> ORDER BY activelayers.lid DESC;
>
> +---+--+---+--+--+
> | name | lid | rsrcc | rsrc | rsrc |
> +---+--+---+--+--+
> | node1 | NULL | 1 | NULL |1 |
> | node2 | NULL | 1 | NULL |2 |
> | node3 | NULL | 1 | NULL |1 |
> | node3 | NULL | 1 | NULL |2 |
> | node0 | NULL | 1 | NULL | NULL |
> | node1 |4 | 2 |1 |1 |
> | node2 |4 | 2 |2 |2 |
> | node3 |4 | 2 |1 |1 |
> | node3 |4 | 2 |2 |2 |
> | node0 | NULL | 2 | NULL | NULL |
> | node1 |3 | 2 |1 |1 |
> | node2 |3 | 2 |2 |2 |
> | node3 |3 | 2 |1 |1 |
> | node3 |3 | 2 |2 |2 |
> | node0 |
Re: why aren't my PRIMARY KEYs being used?
On Oct 29, 2004, at 6:26 AM, [EMAIL PROTECTED] wrote: I think it may be because of your mixed left and right joins. There are several bugs listed that show that the optimizer mishandles certain combinations of left and right joins. SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nr src.rsrc) as matchcount,activenodes.name,activenodes.rsrcc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; This actually didn't produce the same result. I'm doing a RIGHT JOIN rather than a LEFT or INNER JOIN to catch node resources (nrsrc) which do not match layer resources (lrsrc), or nodes with no layer resources at all. This example makes the difference a little clearer: SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc RIGHT JOIN activenodes ON nrsrc.id=activenodes.id ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 | NULL | 1 | NULL |1 | | node2 | NULL | 1 | NULL |2 | | node3 | NULL | 1 | NULL |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |1 |1 | | node3 |4 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |1 |1 | | node3 |3 | 2 |2 |2 | | node0 | NULL | 2 | NULL | NULL | | node1 | NULL | 1 | NULL |1 | | node2 |2 | 1 |2 |2 | | node3 | NULL | 1 | NULL |1 | | node3 |2 | 1 |2 |2 | | node0 | NULL | 1 | NULL | NULL | | node1 |1 | 1 |1 |1 | | node2 | NULL | 1 | NULL |2 | | node3 |1 | 1 |1 |1 | | node3 | NULL | 1 | NULL |2 | | node0 | NULL | 1 | NULL | NULL | | node1 | NULL | 0 | NULL |1 | | node2 | NULL | 0 | NULL |2 | | node3 | NULL | 0 | NULL |1 | | node3 | NULL | 0 | NULL |2 | | node0 | NULL | 0 | NULL | NULL | +---+--+---+--+--+ SELECT activenodes.name,lrsrc.lid,activelayers.rsrcc,lrsrc.rsrc,nrsrc.rsrc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id ORDER BY activelayers.lid DESC; +---+--+---+--+--+ | name | lid | rsrcc | rsrc | rsrc | +---+--+---+--+--+ | node1 |4 | 2 |1 |1 | | node3 |4 | 2 |1 |1 | | node2 |4 | 2 |2 |2 | | node3 |4 | 2 |2 |2 | | node1 |3 | 2 |1 |1 | | node3 |3 | 2 |1 |1 | | node2 |3 | 2 |2 |2 | | node3 |3 | 2 |2 |2 | | node2 |2 | 1 |2 |2 | | node3 |2 | 1 |2 |2 | | node1 |1 | 1 |1 |1 | | node3 |1 | 1 |1 |1 | +---+--+---+--+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why aren't my PRIMARY KEYs being used?
I think it may be because of your mixed left and right joins. There are several bugs listed that show that the optimizer mishandles certain combinations of left and right joins. May I suggest that you re-arrange your query to include only LEFT and INNER joins (at least until the bugs are worked out). The following query should return the same set of records (all records from activelayers with optional records from lrsrc, nrsrc, and activenodes but only if the lrsrc records match with those in activelayers, the nrsrc rows match with rows from lrsrc, and the activenodes rows match with those from nrsrc) SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nrsrc.rsrc) as matchcount,activenodes.name,activenodes.rsrcc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid INNER JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc INNER JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; Usually what people want when they do a LEFT join and a RIGHT join in the same query is something known in other products as a FULL OUTER join. You can duplicate that behavior by running two queries (one directed from the "left", the other from the "right") and unioning their results together. Let me know if that was what you were after and I will help you to transform this into a FULL OUTER join. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Laszlo Thoth <[EMAIL PROTECTED]> wrote on 10/28/2004 08:15:20 PM: > I've created two temporary tables: > > CREATE TEMPORARY TABLE `activenodes` ( > `id` smallint(6) unsigned NOT NULL default '0', > `name` varchar(50) NOT NULL default '', > `rsrcc` bigint(21) NOT NULL default '0', > PRIMARY KEY (`id`) > ); > > CREATE TEMPORARY TABLE `activelayers` ( > `id` int(10) unsigned NOT NULL default '0', > `lid` tinyint(3) unsigned NOT NULL default '0', > `rsrcc` bigint(21) NOT NULL default '0', > PRIMARY KEY (`id`,`lid`) > ); > > I've also got two non-temporary tables: > > CREATE TABLE `nrsrc` ( > `id` smallint(6) unsigned NOT NULL default '0', > `rsrc` smallint(6) unsigned NOT NULL default '0', > PRIMARY KEY (`id`,`rsrc`), > KEY `rsrc` (`rsrc`) > ); > > CREATE TABLE `lrsrc` ( > `id` int(10) unsigned NOT NULL default '0', > `lid` tinyint(3) unsigned NOT NULL default '0', > `rsrc` smallint(6) unsigned NOT NULL default '0', > PRIMARY KEY (`id`,`lid`,`rsrc`), > KEY `rsrc` (`rsrc`) > ); > > > I'm attempting to perform the following join: > > SELECT > activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc. > rsrc=nrsrc.rsrc) > as matchcount,activenodes.name,activenodes.rsrcc > FROM activelayers > LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid > RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc > RIGHT JOIN activenodes ON nrsrc.id=activenodes.id > GROUP BY activelayers.id,activelayers.lid,activenodes.id > HAVING matchcount=activelayers.rsrcc > ORDER BY activelayers.lid DESC; > > My EXPLAIN tells me that I will not be using either of the primary keys in my > temporary tables: > > ++-+--++--- > +-+- > +- > +--+-+ > | id | select_type | table| type | possible_keys | key > | key_len | > ref | rows | > Extra | > ++-+--++--- > +-+- > +- > +--+-+ > | 1 | SIMPLE | activenodes | ALL| NULL | NULL > |NULL | > NULL| 3 | > Using temporary; Using filesort | > | 1 | SIMPLE | nrsrc| ref| PRIMARY | PRIMARY > | 2 | > sherman.activenodes.id | 2 | > Using index | > | 1 | SIMPLE | activelayers | ALL| NULL | NULL > |NULL | > NULL > |6 | > | > | 1 | SIMPLE | lrsrc| eq_ref | PRIMARY,rsrc | PRIMARY > | 7 | > sherman.activelayers.id,sherman.activelayers.lid,sherman.nrsrc.rsrc | 1 | > Using index | > ++-+--++--- > +-+- > +- > +--+-+ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: why aren't my PRIMARY KEYs being used?
Hi. See: http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html http://dev.mysql.com/doc/mysql/en/EXPLAIN.html It is ok that MySQL doesn't use indexes when selecting indexed columns that are compared using the = operator. You can read how indexes works. Laszlo Thoth <[EMAIL PROTECTED]> wrote: > I've created two temporary tables: > > CREATE TEMPORARY TABLE `activenodes` ( > `id` smallint(6) unsigned NOT NULL default '0', > `name` varchar(50) NOT NULL default '', > `rsrcc` bigint(21) NOT NULL default '0', > PRIMARY KEY (`id`) > ); > > CREATE TEMPORARY TABLE `activelayers` ( > `id` int(10) unsigned NOT NULL default '0', > `lid` tinyint(3) unsigned NOT NULL default '0', > `rsrcc` bigint(21) NOT NULL default '0', > PRIMARY KEY (`id`,`lid`) > ); > > I've also got two non-temporary tables: > > CREATE TABLE `nrsrc` ( > `id` smallint(6) unsigned NOT NULL default '0', > `rsrc` smallint(6) unsigned NOT NULL default '0', > PRIMARY KEY (`id`,`rsrc`), > KEY `rsrc` (`rsrc`) > ); > > CREATE TABLE `lrsrc` ( > `id` int(10) unsigned NOT NULL default '0', > `lid` tinyint(3) unsigned NOT NULL default '0', > `rsrc` smallint(6) unsigned NOT NULL default '0', > PRIMARY KEY (`id`,`lid`,`rsrc`), > KEY `rsrc` (`rsrc`) > ); > > > I'm attempting to perform the following join: > > SELECT > activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nrsrc.rsrc) > as matchcount,activenodes.name,activenodes.rsrcc > FROM activelayers > LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid > RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc > RIGHT JOIN activenodes ON nrsrc.id=activenodes.id > GROUP BY activelayers.id,activelayers.lid,activenodes.id > HAVING matchcount=activelayers.rsrcc > ORDER BY activelayers.lid DESC; > > My EXPLAIN tells me that I will not be using either of the primary keys in my > temporary tables: > > ++-+--++---+-+-+-+--+-+ > | id | select_type | table| type | possible_keys | key | key_len | > ref | rows | > Extra | > ++-+--++---+-+-+-+--+-+ > | 1 | SIMPLE | activenodes | ALL| NULL | NULL|NULL | > NULL|3 | > Using temporary; Using filesort | > | 1 | SIMPLE | nrsrc| ref| PRIMARY | PRIMARY | 2 | > sherman.activenodes.id |2 | > Using index | > | 1 | SIMPLE | activelayers | ALL| NULL | NULL|NULL | > NULL|6 | >| > | 1 | SIMPLE | lrsrc| eq_ref | PRIMARY,rsrc | PRIMARY | 7 | > sherman.activelayers.id,sherman.activelayers.lid,sherman.nrsrc.rsrc |1 | > Using index | > ++-+--++---+-+-+-+--+-+ > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why aren't my PRIMARY KEYs being used?
I've created two temporary tables: CREATE TEMPORARY TABLE `activenodes` ( `id` smallint(6) unsigned NOT NULL default '0', `name` varchar(50) NOT NULL default '', `rsrcc` bigint(21) NOT NULL default '0', PRIMARY KEY (`id`) ); CREATE TEMPORARY TABLE `activelayers` ( `id` int(10) unsigned NOT NULL default '0', `lid` tinyint(3) unsigned NOT NULL default '0', `rsrcc` bigint(21) NOT NULL default '0', PRIMARY KEY (`id`,`lid`) ); I've also got two non-temporary tables: CREATE TABLE `nrsrc` ( `id` smallint(6) unsigned NOT NULL default '0', `rsrc` smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`rsrc`), KEY `rsrc` (`rsrc`) ); CREATE TABLE `lrsrc` ( `id` int(10) unsigned NOT NULL default '0', `lid` tinyint(3) unsigned NOT NULL default '0', `rsrc` smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`lid`,`rsrc`), KEY `rsrc` (`rsrc`) ); I'm attempting to perform the following join: SELECT activelayers.id,activelayers.lid,activelayers.rsrcc,COUNT(lrsrc.rsrc=nrsrc.rsrc) as matchcount,activenodes.name,activenodes.rsrcc FROM activelayers LEFT JOIN lrsrc ON lrsrc.id=activelayers.id AND lrsrc.lid=activelayers.lid RIGHT JOIN nrsrc ON lrsrc.rsrc=nrsrc.rsrc RIGHT JOIN activenodes ON nrsrc.id=activenodes.id GROUP BY activelayers.id,activelayers.lid,activenodes.id HAVING matchcount=activelayers.rsrcc ORDER BY activelayers.lid DESC; My EXPLAIN tells me that I will not be using either of the primary keys in my temporary tables: ++-+--++---+-+-+-+--+-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--++---+-+-+-+--+-+ | 1 | SIMPLE | activenodes | ALL| NULL | NULL|NULL | NULL|3 | Using temporary; Using filesort | | 1 | SIMPLE | nrsrc| ref| PRIMARY | PRIMARY | 2 | sherman.activenodes.id |2 | Using index | | 1 | SIMPLE | activelayers | ALL| NULL | NULL|NULL | NULL|6 | | | 1 | SIMPLE | lrsrc| eq_ref | PRIMARY,rsrc | PRIMARY | 7 | sherman.activelayers.id,sherman.activelayers.lid,sherman.nrsrc.rsrc |1 | Using index | ++-+--++---+-+-+-+--+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Primary Keys
I understand your question. I am so sorry to be so slow today :-) You want to know how to create a PRIMARY KEY that is composed of more than one column. Most of the time when we declare a PRIMARY KEY on a table, we do it by putting the keywords "PRIMARY KEY" at the end of the column definition, like this: CREATE TABLE example1( ID int not null auto_increment primary key, field2 char(5) , ... more fields ... ) But if you need more than one column to define the PRIMARY KEY for a table you CANNOT say: CREATE TABLE example2( id_table1 int not null primary key, id_table2 int not null primary key ) because, in MySQL that is a syntax error. What you need is: CREATE TABLE example3 ( ID int auto_increment, id_table1 int not null, id_table2 int not null, PRIMARY KEY(id_table1, id_table2) ) You may be able to read: http://dev.mysql.com/doc/mysql/pt/CREATE_TABLE.html#IDX1582 for a better explanation. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rui Monteiro" <[EMAIL PROTECTED]> wrote on 07/20/2004 01:02:36 PM: > Hello, > > The example I gave you has 2 foreign keys that are primary keys on that table. > > Heres na example > > Costumer > Id (PK) > Name > > Shopping list > ID (PK) > ID_costumer > ID_product > > > The relationship between these 2 tables is ?infinite? to ?infinite?. > The way to resolve this is by creating a table in the middle like this: > > COS/SHOP > ID_Cust (PK) > ID_Shop (PK) > > Costumer ? 1 : N ? COS/SHOP > Shopping list ? 1 : N ? COS/SHOP > > Thanks > > > > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Enviada: terça-feira, 20 de Julho de 2004 17:06 > Para: Rui Monteiro > Cc: [EMAIL PROTECTED] > Assunto: Re: Primary Keys > > > Your example has 1 Primary Key and 2 Foreign Keys. > > Please post a sample data structure and state (not in SQL) what > situation you want to achieve. If you need more constraints on the > table to prevent creating duplicates you can create additional > UNIQUE Keys but, by definition, any table should have only one Primary Key. > > Yours, > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > "Rui Monteiro" <[EMAIL PROTECTED]> wrote on 07/20/2004 11:54:00 AM: > > > Mello, > > > > > > > > I was wondering why canto r how can I put 2 primary keys on a table? > > > > > > > > Here's na example on Oracle language: > > > > > > > > CREATE TABLE FacturaMusica( > > > > CodFactura number(4), CONSTRAINTS FK_FacturaMusica_CodFactura > > FOREIGN KEY(CodFactura) REFERENCES Factura(CodFactura), > > > > CodMusica number(4), CONSTRAINTS FK_FacturaMusica_CodMusica > > FOREIGN KEY(CodMusica) REFERENCES Musica(CodMusica), > > > > CONSTRAINT PK_FacturaMusica PRIMARY KEY(CodFactura,CodMusica) > > > > ); > > > > > > > > This is very usefull to break "n" to "n" relations. > > > > > > > > Any tip? > > > > > > > > Thanks > > > > > > > > Rui > >
Re: Primary Keys
Your example has 1 Primary Key and 2 Foreign Keys. Please post a sample data structure and state (not in SQL) what situation you want to achieve. If you need more constraints on the table to prevent creating duplicates you can create additional UNIQUE Keys but, by definition, any table should have only one Primary Key. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rui Monteiro" <[EMAIL PROTECTED]> wrote on 07/20/2004 11:54:00 AM: > Mello, > > > > I was wondering why canto r how can I put 2 primary keys on a table? > > > > Here's na example on Oracle language: > > > > CREATE TABLE FacturaMusica( > > CodFactura number(4), CONSTRAINTS FK_FacturaMusica_CodFactura > FOREIGN KEY(CodFactura) REFERENCES Factura(CodFactura), > > CodMusica number(4), CONSTRAINTS FK_FacturaMusica_CodMusica > FOREIGN KEY(CodMusica) REFERENCES Musica(CodMusica), > > CONSTRAINT PK_FacturaMusica PRIMARY KEY(CodFactura,CodMusica) > > ); > > > > This is very usefull to break "n" to "n" relations. > > > > Any tip? > > > > Thanks > > > > Rui >
Primary Keys
Mello, I was wondering why canto r how can I put 2 primary keys on a table? Here's na example on Oracle language: CREATE TABLE FacturaMusica( CodFactura number(4), CONSTRAINTS FK_FacturaMusica_CodFactura FOREIGN KEY(CodFactura) REFERENCES Factura(CodFactura), CodMusica number(4), CONSTRAINTS FK_FacturaMusica_CodMusica FOREIGN KEY(CodMusica) REFERENCES Musica(CodMusica), CONSTRAINT PK_FacturaMusica PRIMARY KEY(CodFactura,CodMusica) ); This is very usefull to break "n" to "n" relations. Any tip? Thanks Rui
Re: Primary keys
"JOHN MEYER" <[EMAIL PROTECTED]> wrote: > > > So I can do something like "SELECT * FROM " . $tablename . "WHERE _rowid=" > $id (I'm using PHP and the primary key is a unique integer column in each of > the tables). Yes. For example: mysql> CREATE TABLE t1(id int NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES(1),(2),(3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1 WHERE _rowid=2; ++ | id | ++ | 2 | ++ 1 row in set (0.00 sec) >>From: Egor Egorov <[EMAIL PROTECTED]> >>To: [EMAIL PROTECTED] >>Subject: Re: Primary keys >>Date: Wed, 24 Mar 2004 13:28:58 +0200 >> >>"JOHN MEYER" <[EMAIL PROTECTED]> wrote: >> > Is there a way to do a SQL Select and get a record without specifying >>the >> > primary key. Example. I have two tables CANDLE and VOTIVES. Can I >>define >> > a select that says "SELECT * FROM TABLE WHERE PRIMARY_KEY="... >> > >> >>No, You can't. >>Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer >>to that column as _rowid: >> http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys
So I can do something like "SELECT * FROM " . $tablename . "WHERE _rowid=" $id (I'm using PHP and the primary key is a unique integer column in each of the tables). From: Egor Egorov <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: Re: Primary keys Date: Wed, 24 Mar 2004 13:28:58 +0200 "JOHN MEYER" <[EMAIL PROTECTED]> wrote: > Is there a way to do a SQL Select and get a record without specifying the > primary key. Example. I have two tables CANDLE and VOTIVES. Can I define > a select that says "SELECT * FROM TABLE WHERE PRIMARY_KEY="... > No, You can't. Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer to that column as _rowid: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Find a broadband plan that fits. Great local deals on high-speed Internet access. https://broadband.msn.com/?pgmarket=en-us/go/onm00200360ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Primary keys
"JOHN MEYER" <[EMAIL PROTECTED]> wrote: > Is there a way to do a SQL Select and get a record without specifying the > primary key. Example. I have two tables CANDLE and VOTIVES. Can I define > a select that says "SELECT * FROM TABLE WHERE PRIMARY_KEY="... > No, You can't. Only if PRIMARY or UNIQUE KEY consists of one integer column, you can refer to that column as _rowid: http://www.mysql.com/doc/en/CREATE_TABLE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Primary keys
Is there a way to do a SQL Select and get a record without specifying the primary key. Example. I have two tables CANDLE and VOTIVES. Can I define a select that says "SELECT * FROM TABLE WHERE PRIMARY_KEY="... John Meyer _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Referencing multiple primary keys
I solved it myself :) You need to keep the primary keys in the same order as the index, DOH! ofcourse :) In parant table PRIMARY KEY(key1,key2) In child table INDEX(key_fk1, key_fk2) Regards /Jonas - Original Message - From: "Jonas Lndén" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 11, 2004 2:49 PM Subject: Referencing multiple primary keys Hello, I am banging my head against the wall trying to figure out how to construct a foreign key towards a table with a primary key consisting of two columns. I have been googling around and thought this would work, but it just gives me an ERROR 1005. ALTER TABLE testDB ADD FOREIGN KEY (fkey1, fkey2) REFERENCES secondDB(col1, col2) ON DELETE SET NULL; Is there someone who could point me in the right direction? I have indexed the foreign keys in testDB. Regards /Jonas Lindén -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Referencing multiple primary keys
Hello, I am banging my head against the wall trying to figure out how to construct a foreign key towards a table with a primary key consisting of two columns. I have been googling around and thought this would work, but it just gives me an ERROR 1005. ALTER TABLE testDB ADD FOREIGN KEY (fkey1, fkey2) REFERENCES secondDB(col1, col2) ON DELETE SET NULL; Is there someone who could point me in the right direction? I have indexed the foreign keys in testDB. Regards /Jonas Lindén
RE: Indexes and multi-column Primary Keys
Hmmm... I fixed the problem but I still don't know what caused it. That same query now produces the below EXPLAIN result and now runs 70% faster. I ran OPTIMIZE TABLE on Spells and Classes, that made Spl rows 1, then I forced the Cls table to use the Object index and it worked. Now, I can't get it to reproduce the problem. The database was just created and populated, so it couldn't have been DB degradation. Any ideas? Chris +-++--+--+-+-+--+--- ---+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +-++--+--+-+-+--+--- ---+ | NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL | 6034 | Using index; Using temporary; Using filesort | | ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 | const,NPCSpl.FileID,NPCSpl.ObjectID |1 | Using where; Using index | | NPCs| eq_ref | PRIMARY,ObjectID | PRIMARY | 6 | ObjNPCs.FileID,ObjNPCs.ObjectID |1 | | | Spl | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell |1 | | | ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 | const,Spl.FileID,Spl.ObjectID |1 | Using where; Using index | | Cls | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCs.ObjectID_Class |1 | Using where | | ObjCls | eq_ref | PRIMARY | PRIMARY | 8 | const,Cls.FileID,Cls.ObjectID |1 | Using where; Using index | +-++--+--+-+-+--+--- ---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexes and multi-column Primary Keys
Hi, I'm not sure I understand indexes properly.
In this EXPLAIN, I expected the Cls (Classes) table to be of type ref with
the key being ObjectID. I'm joining both identically as far as I can tell,
on one of the columns in the primary key, which is set to a key itself, but
Cls is joining on ALL. Why is it doing that?
Has it got something to do with the way my query is worded?
Thanks in advance,
Chris
=EXPLAIN===
+-++--+--+-+-+--+---
---+
| table | type | possible_keys| key | key_len | ref
| rows | Extra
|
+-++--+--+-+-+--+---
---+
| NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL
| 6034 | Using index; Using
temporary; Using filesort |
| ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 |
const,NPCSpl.FileID,NPCSpl.ObjectID |1 | Using where; Using index
|
| NPCs| eq_ref | PRIMARY,ObjectID | PRIMARY | 6 |
ObjNPCs.FileID,ObjNPCs.ObjectID |1 |
|
| Spl | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell
| 15 |
|
| ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 |
const,Spl.FileID,Spl.ObjectID |1 | Using where; Using index
|
| Cls | ALL| PRIMARY,ObjectID | NULL |NULL | NULL
| 84 | Using where
|
| ObjCls | eq_ref | PRIMARY | PRIMARY | 8 |
const,Cls.FileID,Cls.ObjectID |1 | Using where; Using index
|
+-++--+--+-+-+--+---
---+
=QUERY=
SELECT
NPCs.FileID,
NPCs.ObjectID,
NPCs.Name,
Spl.FileID as FileID_Spell,
Spl.ObjectID as ObjectID_Spell,
Spl.Name as Name_Spell
FROM NPCs
JOIN GroupedObjects ObjNPCs
ON
(
1=ObjNPCs.GroupID
AND
NPCs.FileID=ObjNPCs.FileID
AND
NPCs.ObjectID=ObjNPCs.ObjectID
)
JOIN NPCSpells NPCSpl
ON
(
ObjNPCs.FileID=NPCSpl.FileID
AND
ObjNPCs.ObjectID=NPCSpl.ObjectID
)
JOIN Spells Spl
ON
(
Spl.ObjectID=NPCSpl.ObjectID_Spell
)
INNER JOIN GroupedObjects ObjSpl
ON
(
1=ObjSpl.GroupID
AND
Spl.FileID=ObjSpl.FileID
AND
Spl.ObjectID=ObjSpl.ObjectID
)
JOIN Classes Cls
ON
(
Cls.ObjectID=NPCs.ObjectID_Class
)
INNER JOIN GroupedObjects ObjCls
ON
(
1=ObjCls.GroupID
AND
Cls.FileID=ObjCls.FileID
AND
Cls.ObjectID=ObjCls.ObjectID
)
WHERE
2048 & NPCs.Services
OR
2048 & Cls.Services
ORDER BY
NPCs.ObjectID;
=TABLES
CREATE TABLE `Spells` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` char(32) default NULL,
`SpellTypeID` tinyint(4) NOT NULL default '0',
`Cost` int(11) NOT NULL default '0',
`Flags`
set('0x1','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x1
','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1
000','0x2000','0x4000','0x8000') NOT NULL default '',
PRIMARY KEY (`FileID`,`ObjectID`),
KEY `ObjectID` (`ObjectID`)
) TYPE=MyISAM COMMENT='Spells'
CREATE TABLE `Classes` (
`FileID` smallint(5) unsigned NOT NULL default '0',
`ObjectID` int(10) unsigned NOT NULL default '0',
`Name` varchar(32) NOT NULL default '',
`AttrID_Pri0` tinyint(4) NOT NULL default '0',
`AttrID_Pri1` tinyint(4) NOT NULL default '0',
`SpecID` tinyint(4) NOT NULL default '0',
`SkillID_Maj0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Maj4` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min0` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min1` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min2` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min3` tinyint(3) unsigned NOT NULL default '0',
`SkillID_Min4` tinyint(3) unsigned NOT NULL default '0',
`Flags`
set('Playable','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x1000','0x2000','0x4000','0x8000','0x
1','0x2','0x4','0x8','0x10','0x20','0x40','0x80','0x100','0x200','0x400','0x800','0x
1000','0x2000','0x4000','0x8000') NOT NULL default '',
`Services`
set('Weapons'
RE: Newbie - Primary Keys
That's just what I wanted to do. 1 primary key made up with multiple fields. In my case I will be a Date Field for Transaction Date and a Integer field for StoreNumber. Thanks a lot for your advice, guys. :oD -Original Message- From: Mark V [mailto:[EMAIL PROTECTED] Sent: Monday, October 20, 2003 2:34 PM To: [EMAIL PROTECTED] Subject: Re: Newbie - Primary Keys Good points Patrick. Thanks for the follow-up :-) --- Patrick Shoaf <[EMAIL PROTECTED]> wrote: - Mark is correct, you can define only 1 Primary Key, but the primary keycan be made up of multiple fields. Example: CREATE TABLE test (item_code char(5) NOT NULL,item_color char(5) NOTNULL, data varchar(255), PRIMARY KEY (ITEM_CODE,ITEM_COLOR)); This would allow multiple entries for an item_code and multiple entriesfor item_color, but only 1 entry for a combination. example item_code | item_color | data --- hammer| red | my favorite hammer| blue |nice color nail |red| ugly item nail | green | nice combination with red hammer name |blue | good with red hammer With the option below you can only have 1 hammer, and only 1 nail in theid1 field. You need to decide do you need multiple indexes, or do you need 1 indexwith multiple data fields. At 02:07 PM 10/20/2003, Mark V wrote: Hi Carlos, You can't have 2 primary keys. A table can only have one primary key. You can, however, also define a Unique key, and if you set it up such that it is not null, it will act much like a prmary key: CREATE TABLE test ( id1 INT UNSIGNED NOT NULL, PRIMARY KEY (id1), id2 INT UNSIGNED NOT NULL, UNIQUE KEY (id2), data VARCHAR(25) ); Hope that helps, Mark --- Carlos Vazquez <[EMAIL PROTECTED]> wrote: > Hi all! > > Just wanted to know how do I create a table with two > primary keys. > > Thanks a lot! > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Primary Keys
Good points Patrick. Thanks for the follow-up :-) --- Patrick Shoaf <[EMAIL PROTECTED]> wrote: - Mark is correct, you can define only 1 Primary Key, but the primary keycan be made up of multiple fields. Example: CREATE TABLE test (item_code char(5) NOT NULL,item_color char(5) NOTNULL, data varchar(255), PRIMARY KEY (ITEM_CODE,ITEM_COLOR)); This would allow multiple entries for an item_code and multiple entriesfor item_color, but only 1 entry for a combination. example item_code | item_color | data --- hammer| red | my favorite hammer| blue |nice color nail |red| ugly item nail | green | nice combination with red hammer name |blue | good with red hammer With the option below you can only have 1 hammer, and only 1 nail in theid1 field. You need to decide do you need multiple indexes, or do you need 1 indexwith multiple data fields. At 02:07 PM 10/20/2003, Mark V wrote: Hi Carlos, You can't have 2 primary keys. A table can only have one primary key. You can, however, also define a Unique key, and if you set it up such that it is not null, it will act much like a prmary key: CREATE TABLE test ( id1 INT UNSIGNED NOT NULL, PRIMARY KEY (id1), id2 INT UNSIGNED NOT NULL, UNIQUE KEY (id2), data VARCHAR(25) ); Hope that helps, Mark --- Carlos Vazquez <[EMAIL PROTECTED]> wrote: > Hi all! > > Just wanted to know how do I create a table with two > primary keys. > > Thanks a lot! > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Primary Keys
BTW Carlos, I'm not sure why you would want or need two primary keys in a table, but if find you truly do (as opposed to a PK and some other unique index/key) - there is probably something wrong with your database design. You may need to rethink your design. Do some research and reading on the topics of "Database Design" and "Database Normalization" - that may help. I know when I first started in Databases (especially in designing them), I had some major misconceptions as to what a PK was and how it should be used. The O'Reilly's book "Managing and Using MySQL, 2nd Edition" has a good chapter on the topics. So does "Mastering MySQL 4". But keep in mind these are only *basic* tutorials and high-level coverage of the subject. Also cheek out Paul DuBois' book "MySQL, 2nd Edition". While, if my memory serves me correctly, he does not have a specific chapter on DB design or normalization, he does teach good design concepts as you progress through the book and its examples; and for anything else MySQL related, it is, IMHO, the best book out there. Good luck, Mark --- Mark V <[EMAIL PROTECTED]> wrote: > Hi Carlos, > > You can't have 2 primary keys. A table can only have > one primary key. You can, however, also define a > Unique key, and if you set it up such that it is not > null, it will act much like a prmary key: > > CREATE TABLE test > ( id1 INT UNSIGNED NOT NULL, >PRIMARY KEY (id1), >id2 INT UNSIGNED NOT NULL, >UNIQUE KEY (id2), >data VARCHAR(25) > ); > > Hope that helps, > Mark > > --- Carlos Vazquez <[EMAIL PROTECTED]> wrote: > > Hi all! > > > > Just wanted to know how do I create a table with > two > > primary keys. > > > > Thanks a lot! > > > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Primary Keys
Hi Carlos, You can't have 2 primary keys. A table can only have one primary key. You can, however, also define a Unique key, and if you set it up such that it is not null, it will act much like a prmary key: CREATE TABLE test ( id1 INT UNSIGNED NOT NULL, PRIMARY KEY (id1), id2 INT UNSIGNED NOT NULL, UNIQUE KEY (id2), data VARCHAR(25) ); Hope that helps, Mark --- Carlos Vazquez <[EMAIL PROTECTED]> wrote: > Hi all! > > Just wanted to know how do I create a table with two > primary keys. > > Thanks a lot! > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - Primary Keys
Hi all! Just wanted to know how do I create a table with two primary keys. Thanks a lot!
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
> You have to remember that the manual is kept in sync with the _latest_ > version of the server ... I know. I chose to avoid that in my comments, even though the original post was regarding v3.23.54 > This change happened in 4.0.13 (implictly and quietly) :) I saw that after I checked out the new release notes. Thanks for the heads up, though. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Mark Matthews" <[EMAIL PROTECTED]> To: "Becoming Digital" <[EMAIL PROTECTED]> Cc: "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, 04 June, 2003 18:54 Subject: Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Becoming Digital wrote: [snip] > from http://www.mysql.com/doc/en/CREATE_TABLE.html: > a.. A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT > NULL. If they are not explicitly declared as NOT NULL, it will be done > implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have > only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask > for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, > which doesn't have any NULL columns, as the PRIMARY KEY. > a.. A PRIMARY KEY can be a multiple-column index. However, you cannot create a > multiple-column index using the PRIMARY KEY key attibute in a column > specification. Doing so will mark only that single column as primary. You must > use the PRIMARY KEY(index_col_name, ...) syntax. > > Long story short, regardless of whether or not MySQL complies with SQL92 specs, > it currently doesn't even comply with it's own documentation. That, I will > concur, is a problem. You have to remember that the manual is kept in sync with the _latest_ version of the server ... This change happened in 4.0.13 (implictly and quietly) :) -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+3niPtvXNTca6JD8RAuMfAJ4txHl4x2K+APxe+r1OOjeCs5wltwCfY6vs 6gZHm1VdNwPDIig38PdvdVA= =l2aO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Becoming Digital wrote: [snip] > from http://www.mysql.com/doc/en/CREATE_TABLE.html: > a.. A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT > NULL. If they are not explicitly declared as NOT NULL, it will be done > implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have > only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask > for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, > which doesn't have any NULL columns, as the PRIMARY KEY. > a.. A PRIMARY KEY can be a multiple-column index. However, you cannot create a > multiple-column index using the PRIMARY KEY key attibute in a column > specification. Doing so will mark only that single column as primary. You must > use the PRIMARY KEY(index_col_name, ...) syntax. > > Long story short, regardless of whether or not MySQL complies with SQL92 specs, > it currently doesn't even comply with it's own documentation. That, I will > concur, is a problem. You have to remember that the manual is kept in sync with the _latest_ version of the server ... This change happened in 4.0.13 (implictly and quietly) :) -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA <___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+3niPtvXNTca6JD8RAuMfAJ4txHl4x2K+APxe+r1OOjeCs5wltwCfY6vs 6gZHm1VdNwPDIig38PdvdVA= =l2aO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
Hi, We agree that statements of the form CREATE TABLE table-name (column1 INT PRIMARY KEY) should be legal -- it should not be necessary to say CREATE TABLE table-name (column1 INT PRIMARY KEY NOT NULL) The requirement, that primary keys should explicitly be declared as NOT NULL, was once necessary: that is the SQL-92 entry-level requirement. In SQL-92 intermediate, and in SQL-99, the NOT NULL is implied. MySQL is now moving to SQL-99 compliance, therefore (1) it's true, NOT NULL should be assumed (2) the behaviour has been changed, starting with version 4.0.13. So, in the current MySQL release, CREATE TABLE t (s1 INT PRIMARY KEY) does not return an error message. Regards, Peter Gulutzan 2003-06-04 -- For technical support contracts, visit https://order.mysql.com/?ref=mpgu __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Peter Gulutzan <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Software Architect /_/ /_/\_, /___/\___\_\___/ Edmonton, Canada <___/ www.mysql.com Office: +1 780 472-6838 - This mail sent through IMP: http://horde.org/imp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
I'm unsure what you see as a problem here? The only conflict with relational theory would occur if MySQL *permitted* NULL values in primary keys. According to the error message you received, MySQL is doing a fine job of preventing this. CREATE TABLE test ( col1 INT PRIMARY KEY, col2 INT ); As a test, I created a table (in MySQL 4.0.12) using the above code. MySQL successfully created the table and set 'col1' to NOT NULL. The problems arise, and I assume this is what you attempted, when creating a table as below: CREATE TABLE test ( col1 INT, col2 INT, PRIMARY KEY (col1) ); When attempting to create the table in that manner, I received Error 1171, too. Still, I'm hard pressed to state that this is not in accordance with SQL92 standards because I was unable to find in the standards document where it states that the column must automatically be declared NOT NULL. However, given the following text from the MySQL Manual, I will concur that there is a problem at hand. from http://www.mysql.com/doc/en/CREATE_TABLE.html: a.. A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, it will be done implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY. a.. A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax. Long story short, regardless of whether or not MySQL complies with SQL92 specs, it currently doesn't even comply with it's own documentation. That, I will concur, is a problem. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: "Neil Zanella" <[EMAIL PROTECTED]> To: "MySQL Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, 04 June, 2003 01:03 Subject: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL Hello, I believe that MySQL is in error in reporting the following message just because I did not specify that the PRIMARY KEY should not be NULL. These days there are standards and SQL92, AFAIK, specifies that if the primary key is not explicitly set to NOT NULL then the RDBMS should automatically and silently assume the user means NOT NULL. After all, any half decent book on relational databases out there will tell you that primary keys cannot be null. So why does MySQL do it this way. By doing this MySQL is breaking the portability of my standard SQL code which works so well with postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as distributed with Red Hat 9, which, for some reason has not decided to update their mysql RPMS to MySQL 4 for that release but I am interesting in knowing if this has been fixed in MySQL. I think one of MySQL's goals should be to support standards such as SQL92 (if not SQL99). Even if internally some things don't work as expected, at a minimum, the parsers should be compatible as much as possible, including standard data types and assuming primary keys are not null by default. ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead BTW, for those willing to check it out, Part 1 (as well as other parts) of the SQL standard are available from http://webstore.ansi.org/ for 18 bucks as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also available as ANSI INCITS 135-1992 (R1998) at the same price. Regards, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
Hi Neil. On Wed, Jun 04, 2003 at 02:33:22AM -0230, Neil Zanella wrote: > I believe that MySQL is in error in reporting the following message just > because I did not specify that the PRIMARY KEY should not be NULL. These > days there are standards and SQL92, AFAIK, specifies that if the primary > key is not explicitly set to NOT NULL then the RDBMS should automatically > and silently assume the user means NOT NULL. After all, any half decent > book on relational databases out there will tell you that primary keys > cannot be null. So why does MySQL do it this way. By doing this MySQL is > breaking the portability of my standard SQL code which works so well with > postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to > the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as > distributed with Red Hat 9, which, for some reason has not decided to > update their mysql RPMS to MySQL 4 for that release but I am interesting > in knowing if this has been fixed in MySQL. I think one of MySQL's goals > should be to support standards such as SQL92 (if not SQL99). Even if > internally some things don't work as expected, at a minimum, the > parsers should be compatible as much as possible, including > standard data types and assuming primary keys are not null > by default. This bug was fixed in MySQL 4.0.13, after it was reported to our bug tracking system here: http://bugs.mysql.com/390 As Peter Gulutzan notes in a comment to that bug, the previous behavior was consistent with the SQL-92 standard, but that has been relaxed in SQL-99, and we are currently working towards SQL-99 compliance. Jim Winstead MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
Daniel Kasak wrote: Neil Zanella wrote: SQL92, AFAIK, specifies that if the primary key is not explicitly set to NOT NULL then the RDBMS should automatically and silently assume the user means NOT NULL. Sounds like the end of the world to me ;) I suppose you have to get excited about something... No need to belittle Neil's question; he's quite right about this. One of the main strengths of SQL is its standardization; that makes up for a multitude of other sins within it. Any time an implementation is non-standard, that means extra work in switching from one RDBMS to another, and extra time which must be spent learning the product. There are better challenges to spend one's time and money on than compensating for such deficiencies. (This is not to say that MySQL is a poor product, of course -- it has many wonderful features. *Most* (or perhaps all) SQL implementations don't meet the standard in all ways, and in each such case, it's a problem with the product. Extensions, by the way, are another story... although they may not be in the standard, they allow you to take advantage of something not yet supported by standard SQL. That has the same disadvantages as I pointed out above, but it also has corresponding advantages. Leaving out a feature that was specified in a standard 11 years ago doesn't fall into this category, though! Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
There are versions of the SQL3 document available at http://www.inf.fu-berlin.de/lehre/SS94/einfdb/SQL3/sqlindex.html and http://dbs.uni-leipzig.de/en/lokal/standards.pdf, free of charge. PB - - Original Message - From: Neil Zanella To: MySQL Mailing List Sent: Wednesday, June 04, 2003 12:03 AM Subject: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL Hello, I believe that MySQL is in error in reporting the following message just because I did not specify that the PRIMARY KEY should not be NULL. These days there are standards and SQL92, AFAIK, specifies that if the primary key is not explicitly set to NOT NULL then the RDBMS should automatically and silently assume the user means NOT NULL. After all, any half decent book on relational databases out there will tell you that primary keys cannot be null. So why does MySQL do it this way. By doing this MySQL is breaking the portability of my standard SQL code which works so well with postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as distributed with Red Hat 9, which, for some reason has not decided to update their mysql RPMS to MySQL 4 for that release but I am interesting in knowing if this has been fixed in MySQL. I think one of MySQL's goals should be to support standards such as SQL92 (if not SQL99). Even if internally some things don't work as expected, at a minimum, the parsers should be compatible as much as possible, including standard data types and assuming primary keys are not null by default. ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead BTW, for those willing to check it out, Part 1 (as well as other parts) of the SQL standard are available from http://webstore.ansi.org/ for 18 bucks as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also available as ANSI INCITS 135-1992 (R1998) at the same price. Regards, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
Neil Zanella wrote: Hello, I believe that MySQL is in error in reporting the following message just because I did not specify that the PRIMARY KEY should not be NULL. These days there are standards and SQL92, AFAIK, specifies that if the primary key is not explicitly set to NOT NULL then the RDBMS should automatically and silently assume the user means NOT NULL. After all, any half decent book on relational databases out there will tell you that primary keys cannot be null. So why does MySQL do it this way. By doing this MySQL is breaking the portability of my standard SQL code which works so well with postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as distributed with Red Hat 9, which, for some reason has not decided to update their mysql RPMS to MySQL 4 for that release but I am interesting in knowing if this has been fixed in MySQL. I think one of MySQL's goals should be to support standards such as SQL92 (if not SQL99). Even if internally some things don't work as expected, at a minimum, the parsers should be compatible as much as possible, including standard data types and assuming primary keys are not null by default. ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead BTW, for those willing to check it out, Part 1 (as well as other parts) of the SQL standard are available from http://webstore.ansi.org/ for 18 bucks as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also available as ANSI INCITS 135-1992 (R1998) at the same price. Regards, Neil Sounds like the end of the world to me ;) I suppose you have to get excited about something... -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL: standard SQL compliancy: primary keys: default: should be NOT NULL
Hello, I believe that MySQL is in error in reporting the following message just because I did not specify that the PRIMARY KEY should not be NULL. These days there are standards and SQL92, AFAIK, specifies that if the primary key is not explicitly set to NOT NULL then the RDBMS should automatically and silently assume the user means NOT NULL. After all, any half decent book on relational databases out there will tell you that primary keys cannot be null. So why does MySQL do it this way. By doing this MySQL is breaking the portability of my standard SQL code which works so well with postgreSQL and Oracle 9i. So why donesn't MySQL play nice and abide to the standard? Is this fixed in MySQL 4? I am running MySQL 3.23.54a as distributed with Red Hat 9, which, for some reason has not decided to update their mysql RPMS to MySQL 4 for that release but I am interesting in knowing if this has been fixed in MySQL. I think one of MySQL's goals should be to support standards such as SQL92 (if not SQL99). Even if internally some things don't work as expected, at a minimum, the parsers should be compatible as much as possible, including standard data types and assuming primary keys are not null by default. ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead BTW, for those willing to check it out, Part 1 (as well as other parts) of the SQL standard are available from http://webstore.ansi.org/ for 18 bucks as standard INCITS/ISO/IEC 9075-1-1999. The older standard is also available as ANSI INCITS 135-1992 (R1998) at the same price. Regards, Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remove primary keys
On Wed, Nov 13, 2002 at 05:03:38PM -0200, Silmara wrote: > I'm working with MySQL-Innodb and I have one table with primary key composed > by the 5 first fields, and I want to change this removing the primary keys > attributes and add one new field in the first position as primary key and > auto_increment. How can I do this? I'd suggest using the ALTER TABLE command as described in the manual. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,337,423 queries (244/sec. avg) - 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
