List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:April 29 2008 1:53pm
Subject:Re: Optimizing table (shall I create a primary field?)
View as plain text  
On Tue, Apr 29, 2008 at 5:09 AM, Charles Lambach
<charles.lambach@stripped> wrote:
> Thank you all for your suggestions.
>
>  So it's very important to make primary fields be as smaller as possible,
>  right? I'm going to change 'isbn' from VARCHAR(100) to VARCHAR(25) and, if
>  possible (I might change my code), from VARCHAR to BIGINT.
>
>  By the way, which are optimal values for "key_len" parameter when doing
>  EXPLAIN?
>
>  Regards,
>  --Charles
>
"The key_len column indicates the length of the key that MySQL decided
to use. The length is NULL if the key column says NULL. Note that the
value of key_len enables you to determine how many parts of a
multiple-part key MySQL actually uses."
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Note: that is length in bytes.

I don't know how much faster your queries will get by reducing the
varchar size, I have not had to deal with many performance issues of
this type. If it is effective (you are noting query times, right?) you
should be able to further reduce the size by change the character set
from multi-btye utf-8 to single byte ascii. The query would be
something like:
ALTER TABLE `books` CHANGE `isbn` `isbn` VARCHAR( 25) CHARACTER SET
ascii NOT NULL

-- 
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