On Thu, 14 Jun 2001, Jeremy Zawodny wrote:
> I have a few comments and questions:
>
> (1) Is this a unique index? If not, consider doing a partial index
> on the column. Index the first 32 characters or so. I'll save a
> lot of disk I/O, memory, and CPU time but still yeild pretty
> good results (depending on your data, of course).
No, it isn't a unique index. Most of the entries are <32 chars, but I used
a varchar(255) to be safe, as it only occupies the number of bytes
required to record the string, plus 1 byte to record the length. There are
77million records, and I can't guarantee that they are all <32. Will the
index know to only index the required number of chars, or will it do 255?
What if I had used a TEXT datatype?
> (2) What are your key_buffer and record_buffer set to? I believe
> that when MySQL is doing serious scanning (as needed in building
> an index), a larger record_buffer will help.
Unfortunately, my key_buffer is only adjusted to 64M, and the
record_buffer is set at a piddling 131072... I only got into configuring
mysql after I began the indexing. The other box (same specs) that I'm
running is set with:
set-variable=key_buffer=128M
set-variable=table_cache=512
set-variable=sort_buffer=8M
set-variable=record_buffer=2M
I'm trying to index on that machine at the same time, by indexing as I
insert. I'm guessing 2M is still far too small? Can you recommend a better
config?
> (3) Try running `vmstat 5' on the box for a bit. Is the CPU pegged?
> Is it I/O bound? Is it swapping at all?
Forgot to mention I'm running Win2K, don't have vmstat, sorry. Perfmon is
shite, too.
> > Please don't tell me this will take twice the age of the universe to
> > finish...
>
> How about 1/2 the age of the universe. But since there's still a fair
> amount of debate over the true age of the universe, I probably haven't
> helped much, have I? :-)
Okay, 1/2 the age is fine. Twice the age would just be silly.
Thanks a ton,
-robin
> Jeremy
> --
> Jeremy D. Zawodny, <[EMAIL PROTECTED]>
> Technical Yahoo - Yahoo Finance
> Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
>
> MySQL 3.23.29: up 19 days, processed 136,385,255 queries (79/sec. avg)
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
