Help with a join select

[Shane Wegner]

Hi all,

I am not sure if this is the appropriate place to ask about this and
appologise if it isn't.  I am having problems with a select in that it
works but is much slower than any other select.  It uses indexes as well
so I am at a loss as to why this is the case.

The select is supposed to return the last 30 closing values for a symbol
where the last value is on the date given.  The following tables are
involved (stripped for this email of corse).

create table symdata(
id bigint default 0 not null auto_increment,
symbol_id int default 0 not null,
date_id smallint default 0 not null,
close double(22,3) default 0 not null,
primary key(id),
index id_index(date_id,symbol_id)
);

create table symbol(
id int default 0 not null auto_increment,
symbol varchar(10) default '' not null,
primary key(id),
index id_and_symbol_index(id,symbol)
);

create table _date(
id smallint default 0 not null auto_increment,
_date date default 0 not null,
primary key(id),
index id_and_date_index(id,_date)
);

So to get the last 30 closing values for symbol BCE as of Jun 1, 1998, I
do the following select.

select symdata.close from symdata, symbol, _date where symdata.symbol_id =
symbol.id and symbol.symbol = 'bce' and symdata.date_id = _date.id and
to_days(_date._date) <= to_days('1998-06-01') order by _date._date desc
limit 30

It takes approximately 30 seconds.  symdata has about 2,000,000 entries
and symbol about 6,000 entries.

mysql> explain select symdata.close from symdata, symbol, _date where
symdata.symbol_id =
    -> symbol.id and symbol.symbol = 'bce' and symdata.date_id = _date.id
and
    -> to_days(_date._date) <= to_days('1998-06-01') order by _date._date
desc
    -> limit 30
    -> \g
+---------+--------+---------------------------------------------------+---------+---------+-----------------+---------+--------------------------------+
| table   | type   | possible_keys                                     |
key     | key_len | ref             | rows    | Extra
|
+---------+--------+---------------------------------------------------+---------+---------+-----------------+---------+--------------------------------+
| symbol  | range  | PRIMARY,symbol_index,id_index,id_and_symbol_index |
NULL    |    NULL | NULL            |       1 | range used on key
symbol_index |
| symdata | ALL    | NULL                                              |
NULL    |    NULL | NULL            | 2772635 | where used
|
| _date   | eq_ref | PRIMARY,id_index,id_and_date_index                |
PRIMARY |       2 | symdata.date_id |       1 | where used
|
+---------+--------+---------------------------------------------------+---------+---------+-----------------+---------+--------------------------------+
3 rows in set (0.03 sec)


It looks alright but perhapse there is something I am overlooking here.
If anyone has any suggestions, I would really appreciate them.

Thanks in advance,
Shane

-- 
Shane Wegner: shane@stripped
Sysadmin, Continuum Systems: http://www.cm.nu	Tel: [phone]
Personal website: http://www.cm.nu/~shane	Fax: [phone]
PGP: keyid:       2048/F5C2BD91			ICQ UIN: 120000
     Fingerprint: 8C 48 B9 D8 53 BB D8 EF
                  76 BB DB A2 1C 0D 1D 87

Attachment: [application/pgp-signature]