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
