On Fri, Mar 16, 2001 at 12:32:37AM +1030, Dan Makovec wrote:
> Hi all,
>
> I'm having problems getting reasonable speed queries from a simple table.
>
...
> I've created a simple table that contains stock exchange data, as follows:
>
> CREATE TABLE shares (
> date date DEFAULT '0000-00-00' NOT NULL,
> symbol char(10) DEFAULT '' NOT NULL,
> open float(10,2) DEFAULT '0.00' NOT NULL,
> close float(10,2) DEFAULT '0.00' NOT NULL,
> high float(10,2) DEFAULT '0.00' NOT NULL,
> low float(10,2) DEFAULT '0.00' NOT NULL,
> volume int(11) DEFAULT '0' NOT NULL,
> PRIMARY KEY (date,symbol),
> KEY symbol_index (symbol(4)),
> KEY symbol_date_index (symbol(4),date)
> );
>
>From what I've read, symbol_index is of no use here, because it is contained
in symbol_date_index. It would hardly influence your select time, but will
influence your insert time. Just thought I should mention it. Search the list
and the documentation for more info on this.
> And I've filled this with a bit of data. Well, more than a bit - 5,791,651 rows
>actually. Still, while this is large, it doesn't seem rediculously huge.
>
> Let's say I want to perform a query to select the last 22 days worth of closing
>prices for stock ABC. I run the query:
>
> mysql> select close from shares where symbol='abc' and date < current_date() order
>by date desc limit 22;
>
> ... Data eventually received ...
>
> 22 rows in set (12.23 sec)
>
> mysql> explain select close from shares where symbol='abc' and date < current_date()
>order by date desc limit 22;
>
>+--------+------+----------------------------------------+--------------+---------+------+------+-------+
> | table | type | possible_keys | key | key_len |
>ref | rows | Extra |
>
>+--------+------+----------------------------------------+--------------+---------+------+------+-------+
> | shares | ref | PRIMARY,symbol_index,symbol_date_index | symbol_index | 4 |
>abc | 4475 | |
>
>+--------+------+----------------------------------------+--------------+---------+------+------+-------+
>
It uses your symbol_index so it scans (almost?) all 4475 rows for the 'abc'
symbol. After scanning all of them, it will sort the result (I think).
What you want is MySQL to use a range of records in symbol_date_index:
select close from shares where symbol='abc' and date between '2001-02-21' and
'2001-03-15';
This should use the correct index and scan only the rows you actually need. I
assume there is a maximum of one record per day per symbol. You might want to
use MySQL date functions to calculate (constant!) values instead of doing
this in your script.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
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
