List:General Discussion« Previous MessageNext Message »
From:Sebastian Mendel Date:April 30 2008 6:02am
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
Charles Lambach schrieb:
> Hi.
> 
> My hosting provider recommended me to optimize my 200,000 record table in
> order to save resources.
> 
> I do _always_ this query:
> SELECT * FROM books WHERE isbn='foo' LIMIT 1
> 
> The primary key of this table was 'id', and 'isbn' was and INDEX field.
> 
> I've modified this:
> ALTER TABLE books DROP PRIMARY KEY, ADD INDEX ('isbn')
> ALTER TABLE books ADD PRIMARY KEY ('isbn')
> 
> Is this a good change? Am I going to waste less resources with 'isbn' field
> as primary key?

IMO not, but this depends on your app,

the Primary Key should be a value that never changes in lifetime of a row, 
and should never be re-used once deleted

if you ever happen to change your ISBN cause by a typo or something, than 
your references to other tables need to be updated too

having `id` as primary key is good
and leave the ISBN unique

you can cut down the index length by half the ISBN length, this should be 
more than enough

according to http://en.wikipedia.org/wiki/International_Standard_Book_Number
you can use a fixed width unsigned INT field with a length of 13 for your ISBN

but you will loose formating ...

or you use two fields, one with formated ISBN and one indexed with numeric ISBN

-- 
Sebastian Mendel
Thread
Optimizing table (shall I create a primary field?)Charles Lambach27 Apr
  • Re: Optimizing table (shall I create a primary field?)Rob Wultsch27 Apr
    • Re: Optimizing table (shall I create a primary field?)Charles Lambach28 Apr
      • Re: Optimizing table (shall I create a primary field?)Rob Wultsch28 Apr
        • Re: Optimizing table (shall I create a primary field?)Rob Wultsch28 Apr
          • Re: Optimizing table (shall I create a primary field?)Wm Mussatto28 Apr
            • Re: Optimizing table (shall I create a primary field?)Charles Lambach29 Apr
              • Re: Optimizing table (shall I create a primary field?)Rob Wultsch29 Apr
  • Re: Optimizing table (shall I create a primary field?)Sebastian Mendel30 Apr