List:General Discussion« Previous MessageNext Message »
From:Scott Hess Date:August 27 1999 6:39pm
Subject:Re: Huge index file, why?
View as plain text  
I've done some experimenting.  Having it be varchar(32) versus varchar(22)
makes no difference in ISM size, if none of the values have more than 22
characters.  It doesn't make a difference what combination of altering the
column, dropping the index, adding the index, or whatever I do.  This is
probably accounted for by MYSQL's indexing of prefixes, somehow.

I've also experimented with converting the varchar to char, and it didn't
make any difference.

Doing isamchk -r _does_ result in a smaller ISM file.  I'm betting it packs
the keys differently than mysqld does, resulting in an ISM file that's
smaller.

Later,
scott

----- Original Message -----
From: Jules Bean <jmlb2@stripped>
To: Scott Hess <scott@stripped>
Cc: Benjamin Pflugmann <philemon@stripped>; <sinisa@stripped>;
<mysql@stripped>
Sent: Friday, August 27, 1999 1:54 AM
Subject: Re: Huge index file, why?


> Scott Hess wrote:
> >
> > Benjamin Pflugmann <philemon@stripped> wrote:
> > > IMHO, it has nothing to do with the index being a binary tree, but
> > > that MySQL saves any string (VARCHAR as CHAR) in an index with its
> > > whole width. Just think of it as if indexes only know about CHAR.
> >
> > Running version 3.22.15gamma, I have a table with operative elements
like
> > like:
> >
> > CREATE TABLE mytable {
> >     sid varchar(32) binary default '' not null,
> >     primary key (sid)
> > };
> >
> > Since I noticed that the sid was always 22 characters in length, I
figured
> > that I could make it a varchar(22), and thus make the index 1/3 smaller.
I
> > did 'ALTER TABLE mytable CHANGE COLUMN sid sid VARCHAR(22) BINARY
DEFAULT ''
> > NOT NULL;'.  The before and after ISM and ISD files were _exactly_ the
same
> > size.  [I had assumed this would be the case for the ISD.]
>
> Try isamchk -r to rebuild the indexes from scratch. (Or, as Benjamin
> suggests, drop and recreate the index)
>
> I suspect mysql doesn't make big changes like that to the index in real
> time..
>
> Jules
>
>
> --
> /----------------+-------------------------------+---------------------\
> |  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
> |  Jules aka     |                               |  Richmond, Surrey   |
> |  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
> +----------------+-------------------------------+---------------------+
> |  War doesn't demonstrate who's right... just who's left.             |
> |  When privacy is outlawed... only the outlaws have privacy.          |
> \----------------------------------------------------------------------/

Thread
Huge index file, why?Jules Bean26 Aug
  • Huge index file, why?sinisa26 Aug
  • Re: Huge index file, why?Jules Bean26 Aug
    • Re: Huge index file, why?Benjamin Pflugmann26 Aug
      • Re: Huge index file, why?Michael Widenius27 Aug
    • Re: Huge index file, why?Michael Widenius27 Aug
  • Re: Huge index file, why?Jules Bean27 Aug
    • Re: Huge index file, why?Michael Widenius27 Aug
  • Re: Huge index file, why?Scott Hess27 Aug
    • Re: Huge index file, why?Benjamin Pflugmann27 Aug
      • Re: Huge index file, why?Michael Widenius27 Aug
        • Re: Huge index file, why?Benjamin Pflugmann28 Aug
  • Re: Huge index file, why?Jules Bean27 Aug
  • Re: Huge index file, why?Scott Hess27 Aug
    • Re: Huge index file, why?Michael Widenius29 Aug
Re: Huge index file, why?V Yau30 Aug
  • Re: Huge index file, why?Michael Widenius30 Aug