List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:April 28 2008 5:09pm
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
On Mon, April 28, 2008 09:44, Rob Wultsch wrote:
> On Mon, Apr 28, 2008 at 6:49 AM, Rob Wultsch <wultsch@stripped> wrote:
>> 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?
>
> For the record: if performance is good enough I wouldn't change
> anything...
>
> --
> Rob Wultsch
> wultsch@stripped
> wultsch (aim)
>
ISBN field is way too long, I think they just changed it to 13 characters.
  Depending on your application leading '0' may be important so you may be
stuck with a character field.  As was suggested, loose the '-' and spaces.
 I don't think they are standard and I would think its easier to
universally remove them.
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

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