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
