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/Interna...rd_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