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.
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?
Cheerio,
d
----- Original Message -----
From: "Heitzso" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, August 14, 2001 3:03 AM
Subject: Re: SELECT MIN(field) takes minutes on a large table!
> I apologize in advance for jumping in with a hardware suggestion,
> but I just joined the list to sort out some similar questions.
>
> Our production sql server (Microsoft's) is sitting on a box
> that's now outdated w/ 2 200mhz processors and maybe .5G
> of RAM. A query that takes 30 seconds on that box takes
> 10-11 seconds on my desktop 1mhz 1G system (both Win systems).
>
> I've been arguing that hardware is cheap nowadays (just
> upgraded memory of my personal workstation to 1.5G
> for $320) and that sometimes that's the easier route.
>
> I ack that the elegant software solution is a good thing
> and we need to know how to optimize index/query etc.
> and that you cannot always throw hardware
> at a problem, but wanted to toss that out as a possible
> reasonable short term solution for Dan.
>
> BTW, mysql runs the same query on that 1G desktop
> in 7 seconds or 3 seconds faster than Microsoft's SQL Server.
>
> Heitzso
>
> >Dan Makovec wrote:
> >
> >>Hi folks,
> >>
> >>Wondering if anybody can help me with this one.
> >>
> >>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;
> >>
> >>I've put an index on date and symbol combined, and tried putting one on
just date.
> >>
> >>I try to run the following query:
> >>
> >>select min(date) from stockmarket where symbol='abc'
> >>
> >>The query returns me an answer in 30 seconds. Is there a way I can
optimize the table, or approach from a new query direction, to dramatically
improve search times?
> >>
> >>TIA for your help.
> >>
> >>Cheerio,
> >>d.
> >>
>
>
>
>
> ---------------------------------------------------------------------
> 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
