Hi.
On Tue, Aug 14, 2001 at 10:41:16PM +0930, [EMAIL PROTECTED] wrote:
> Hi Heitzso,
>
> Thanks for your suggestion, and in the absence of feedback from anybody else
> I suspect you're probably right. The system this thing is running on is a
> vastly outdated P150 with 128MB.
With 128MB, you table should fit in memory (about 14MB for the index
and about 30MB for the data file, correct?). So maybe you just need to
adjust some server variables.
The query should return in less than a second when you run it a second
time (exactly the same as the first one). Does it?
If so, it means, that you computer simply needs 30 secs to read from
disk all the data it needs to solve the query. Anyhow, 30 secs seems
slow to me even on that hardware.
Ah, well, and you did not show the result of EXPLAIN SELECT ... until
now. Is an index used as it should?
Which version of MySQL do you use (SELECT VERSION())?
> I was hoping that I could do a little trick with the tables to optimize the
> queries a little more and get just that bit more life out of an old system,
> but I guess with a table this size I'm just hitting a hardware related wall
> and the only thing to do is fork out some cash on a new 1.2GHz system or
> something :(
>
> ... Unless anybody has any other ideas?
If the second query needs more than a second, you server variables can
probably be improved. If you have at least 30-40MB free (after
starting MySQL, but before running any query), you should ensure that
the index fits in memory by setting key_buffer_size=15MB (or something
like that).
For more discussion of this, you may want to post the result of
SHOW VARIABLES and SHOW STATUS.
Bye,
Benjamin.
[...]
> > >>I've got a table with 6.2 million rows in it, and MySQL seems to be
> straining a bit with it. It's a basic table storing stock trading prices:
> > >>
> > >>+--------+-------------+------+-----+------------+-------+
> > >>| Field | Type | Null | Key | Default | Extra |
> > >>+--------+-------------+------+-----+------------+-------+
> > >>| date | date | | PRI | 0000-00-00 | |
> > >>| symbol | char(10) | | PRI | | |
> > >>| open | float(10,2) | | | 0.00 | |
> > >>| close | float(10,2) | | | 0.00 | |
> > >>| high | float(10,2) | | | 0.00 | |
> > >>| low | float(10,2) | | | 0.00 | |
> > >>| volume | int(11) | | | 0 | |
> > >>+--------+-------------+------+-----+------------+-------+
> > >>7 rows in set (0.03 sec)
> > >>
> > >>Creation command:
> > >>CREATE TABLE stockmarket (
> > >> date date NOT NULL default '0000-00-00',
> > >> symbol char(10) NOT NULL default '',
> > >> open float(10,2) NOT NULL default '0.00',
> > >> close float(10,2) NOT NULL default '0.00',
> > >> high float(10,2) NOT NULL default '0.00',
> > >> low float(10,2) NOT NULL default '0.00',
> > >> volume int(11) NOT NULL default '0',
> > >> PRIMARY KEY (date,symbol),
> > >> KEY symbol_date_index (symbol(4),date),
> > >> KEY date_index (date)
> > >>) TYPE=MyISAM PACK_KEYS=1;
> > >>
---------------------------------------------------------------------
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
