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.
Hi,
Have tried 'explain' command to check if mysql is using your index ?
May be:
select date from stockmarket where symbol='abc' order by date limit 1
will be faster (according to mysql manual, it should use symbol_date_index).
PS: What version of MySQL are you using ?
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
