List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:April 28 2008 1:49pm
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
I am going to assume that you are asking this question because
performance has not improved from this change. Is this correct?

I don't think that your surogate key (id) is useful, but that is
probably minor. I think that your hostings company suggestion is
probably a good idea, but will also probably not impact performance. A
prymary key is basically only a unique not null key, and it sounds
like your isbn field should have those properties.

>key_len=302
This is pretty horrible. If I needed better performance I would try to
turn the isbn field into a bigint (the dashes are the only non-numeric
characters and have no significance, right?). That would start with
something like:
ALTER TABLE `books` ADD `new_isbn` BIGINT NOT NULL ;
UPDATE `books` SET `new_isbn` = replace(`isbn`,'-','');
...
though that may require significant changes in your app (converting
the string into an int) but a stored procedure might make this
somewhat less painful.

What are your goals here?

-- 
Rob Wultsch
wultsch@stripped
wultsch (aim)
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