I have seen a lot of question and issues around index size and time on
this list lately.
Well you should increase your Key Buffer but
Roger your record size is about 12k per row
Pawan your record size is an enormous 91k per row
You have to remember that RDBMS(for transaction processing) work best
when normalizing data down to allow for simple small per-row data
transactions.
So I would work on normalizing the data down into tables that average
about 4 to 5k per row returned on a SELECT * at most 8k
Never Index on text, blob, and if you can help it don't use var char in
indexes.
While I do agree with Jo and I think you should increase your Key Buffer
that is just a temporay fix. I would improve the architecture of your
database first. Also it is always best to create all the all the indexes
that you think you might need on a table before you put the data in. It
only takes a few seconds to drop an index if you realize you do not need
it afterward.
Wyly Wade
Forefront Inc
1413 S. Howard Ave
Suite 104
Tampa Fl, 33606
813-253-2267
-----Original Message-----
From: Joseph Bueno [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 06, 2001 3:47 AM
To: Roger Ramirez
Cc: [EMAIL PROTECTED]
Subject: Re: indexing
Roger Ramirez wrote:
>
> Hi,
>
> I'm using MySQL 3.23.39 under Red Hat 7.1 on a Pentium III 600mhz with
> 640MB of Ram. I only 1 table in a database with 5.6 million records
and
> I'm currently trying to index a varchar(100) field. I started the
query
> "alter table mytable add index myfield (myfield)" at 10am EST this
morning
> and 5:45 hours later its still running. If I look in my mysql/var
> directory I have the following.
>
> -rw-rw---- 1 root root 8888 Jul 5 09:44 mytable.frm
> -rw-rw---- 1 root root 689493112 Jul 5 09:58 mytable.MYD
> -rw-rw---- 1 root root 114336768 Jul 5 09:58 mytable.MYI
> -rw-rw---- 1 root root 8888 Jul 5 09:59 #sql-312_9.frm
> -rw-rw---- 1 root root 616317108 Jul 5 11:21
#sql-312_9.MYD
> -rw-rw---- 1 root root 136826880 Jul 5 14:30
#sql-312_9.MYI
>
> The very last file #sql-312_9.MYI has been growing at a rate of about
> 40-50MB/hour.
>
> Is there any way for me to tell how much longer this will go on? I
did a
> couple of indexes on two other fields in this table the day
> before yesterday but I issued the commands and walked away from the
> computer. It was a rough day so i don't remember how long it took.
The
> other two fields are varchar(5) and varchar(10) and I know for a fact
that
> it didn't take more then 24 hours, and I'm pretty sure it was less
than 6
> hours.
>
> Thanks,
> Roger Ramirez
>
> ---------------------------------------------------------------------
> 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
Hi,
Check your key_buffer_size parameter and try to make it
as big as you can, for best performance it should be big enough
to hold a copy of all your indexes. If your machine is dedicated
to mysql, I would suggest key_buffer_size=256M.
Regards
--
Joseph Bueno
NetClub/Trader.com
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
