Hmm.. So ID is not PRIMARY KEY (not UNIQUE index)..
That makes it harder. A lot harder. A lot depends now
on how many records per ID you have there on average.
If you you have only, say, 50 ids then you'll be slower.
Basically the query you do MUST BE A SLOW QUERY.
What you can do is set arguments for your mysqld
-O key_buffer=64Mb
-O sort_buffer=4MB
(you might to set them even higher).
However, you must know that you hardware is too
weak for your database. Also, your FreeBSD is old and
might slower mysql down. Upgrade to 4.2-stable.
----- Original Message -----
From: "Manuel Capinha" <[EMAIL PROTECTED]>
To: "Artem Koutchine" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, January 27, 2001 9:27 PM
Subject: Re: 3 million+ records problems.
>
> From a mysqldump:
>
> #
> # Table structure for table 'names'
> #
> CREATE TABLE names (
> id int(10) unsigned DEFAULT '0' NOT NULL,
> name varchar(80) DEFAULT '' NOT NULL,
> KEY id (id)
> );
>
> The machine is an Intel Celeron 533Mhz, with 64 mb of RAM, with dual
10
> GB EIDE hard drives (doing RAID), running FreeBSD 3.4-Release.
>
>
>
> Artem Koutchine wrote:
>
> > Well then, be so kind and tell us your index configuration
> > and your hardware configuration.
> >
> > ----- Original Message -----
> > From: "Manuel Capinha" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Saturday, January 27, 2001 9:10 PM
> > Subject: 3 million+ records problems.
> >
> > > Hi!
> > >
> > > I've got a table with 3 million+ records. This table has only to
> > > columns: an int and a varchar(80).
> > >
> > > I need to periodically execute a statement like: "SELECT
id,count(*)
> > > FROM names GROUP bY id", which takes very long time to end.
> > >
> > > I'm using perl (DBI) to access this table. When I do a select by
id
> > > (SELECT name FROM names WHERE id=1) it hangs forever for very
large
> > > results (>1million).
> > >
> > > I'm running mysql 3.22.29 on FreeBSD 3.4. Would an upgrade to
the
> > latest
> > > 3.23 help ? Would an upgrade to FreeBSD 4 or maybe Linux help ?
> > > The id column is indexed, but i believe this is A Good
Thing(tm).
> > >
> > > I've checked past messages of this mailing list and i couldn't
find
> > > anything that seemed relevant to my case. Can anyone give me
some
> > > suggestions to speed things up ?
> > >
> > > Thanks in advance.
> > >
> > > Manuel Capinha,
> > > who as just seen his weekend ruined.
> > >
> > >
> >
> --------------------------------------------------------------------
> > -
> > > 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
