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.
