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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
