Re: [PHP-DB] advantages/disads of primary keys and indices?
Hello, > Thank you! this wont slow updates or inserts? Not the updates. The update query will use the index to lookup find the table line that it will update. Then, since you will not change any of the indexed columns the index does not need to be updated too. Manuel Lemos > > - Original Message - > From: "Manuel Lemos" <[EMAIL PROTECTED]> > To: "Noah Spitzer-Williams" <[EMAIL PROTECTED]> > Sent: Saturday, June 23, 2001 1:54 PM > Subject: Re: [PHP-DB] advantages/disads of primary keys and indices? > > > > Hello Noah, > > > > On 23-Jun-01 14:29:58, you wrote: > > > > >Yeah the keys would be part of the first condition of the where clause... > > > > >this table is like this: > > >username | siteid | bannerid | clicks | someotherstat > > > > > > >an update would be like: update tbl set clicks=clicks+1 where > > >username='something' and siteid='something' and bannerid='something' > > > > >a select would be: select clicks from tbl where username='something' and > > >siteid='something' and bannerid='something' > > > > > > >so knowing this, does it make sense to make primary keys out of > > >username,siteid,bannerid? > > > > Yes, you should make a single primary key with all the three fields: > > username, siteid and bannerid. > > > > > > Regards, > > Manuel Lemos > > > > Web Programming Components using PHP Classes. > > Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] > > -- > > E-mail: [EMAIL PROTECTED] > > URL: http://www.mlemos.e-na.net/ > > PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp > > -- > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
Hello Noah, On 23-Jun-01 14:29:58, you wrote: >Yeah the keys would be part of the first condition of the where clause... >this table is like this: >username | siteid | bannerid | clicks | someotherstat >an update would be like: update tbl set clicks=clicks+1 where >username='something' and siteid='something' and bannerid='something' >a select would be: select clicks from tbl where username='something' and >siteid='something' and bannerid='something' >so knowing this, does it make sense to make primary keys out of >username,siteid,bannerid? Yes, you should make a single primary key with all the three fields: username, siteid and bannerid. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
Yeah the keys would be part of the first condition of the where clause... this table is like this: username | siteid | bannerid | clicks | someotherstat an update would be like: update tbl set clicks=clicks+1 where username='something' and siteid='something' and bannerid='something' a select would be: select clicks from tbl where username='something' and siteid='something' and bannerid='something' so knowing this, does it make sense to make primary keys out of username,siteid,bannerid? Thanks! - Noah ""Manuel Lemos"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hello Noah, > > On 23-Jun-01 12:10:20, you wrote: > > >I see, > > >What about if you had 3 or 4 columns that would always be unique, is there > >an advantage to naming them as primary keys? does this slow inserts because > >it has to check if theres a duplicate entry? > > What really slows down is updating the index with the new entry value. > > Anyway, some times it may pay to slow down inserts and updates for very > large tables. You have to check if the indexes/primary keys that you are > adding are used as the first condition of the WHERE clause. > > > > Regards, > Manuel Lemos > > Web Programming Components using PHP Classes. > Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] > -- > E-mail: [EMAIL PROTECTED] > URL: http://www.mlemos.e-na.net/ > PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp > -- > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
Hello Noah, On 23-Jun-01 12:10:20, you wrote: >I see, >What about if you had 3 or 4 columns that would always be unique, is there >an advantage to naming them as primary keys? does this slow inserts because >it has to check if theres a duplicate entry? What really slows down is updating the index with the new entry value. Anyway, some times it may pay to slow down inserts and updates for very large tables. You have to check if the indexes/primary keys that you are adding are used as the first condition of the WHERE clause. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
I see, What about if you had 3 or 4 columns that would always be unique, is there an advantage to naming them as primary keys? does this slow inserts because it has to check if theres a duplicate entry? ""Manuel Lemos"" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hello Noah, > > On 22-Jun-01 21:17:35, you wrote: > > >what are the advantages and disadvantages of primary keys and indices? > > A primary key is a unique index. > > Use indexes for the fields that will be most looked up in the first > condition of the WHERE clause of your SQL queries, especially if your > tables will hold many values, but beware that indexes also take disk space. > > > Regards, > Manuel Lemos > > Web Programming Components using PHP Classes. > Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] > -- > E-mail: [EMAIL PROTECTED] > URL: http://www.mlemos.e-na.net/ > PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp > -- > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
With indices you speed SELECTs, but slow INSERTs and UPDATEs. ""Noah Spitzer-Williams"" <[EMAIL PROTECTED]> ha scritto nel messaggio 9h0n6v$l2h$[EMAIL PROTECTED]">news:9h0n6v$l2h$[EMAIL PROTECTED]... > what are the advantages and disadvantages of primary keys and indices? > > - Noah > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > To contact the list administrators, e-mail: [EMAIL PROTECTED] > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] advantages/disads of primary keys and indices?
Hello Noah, On 22-Jun-01 21:17:35, you wrote: >what are the advantages and disadvantages of primary keys and indices? A primary key is a unique index. Use indexes for the fields that will be most looked up in the first condition of the WHERE clause of your SQL queries, especially if your tables will hold many values, but beware that indexes also take disk space. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?[EMAIL PROTECTED] -- E-mail: [EMAIL PROTECTED] URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] advantages/disads of primary keys and indices?
what are the advantages and disadvantages of primary keys and indices? - Noah -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
