From: Michael Widenius
Date: April 29 1999 12:31am
Subject: Help with a join select
List-Archive: http://lists.mysql.com/mysql/2569
Message-Id: <14119.42661.87664.532644@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Shane" == Shane Wegner <shane@stripped> writes:

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

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

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

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

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

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

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

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

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


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

Hi!

The problem is the second 'ALL' in the 'type' column;  MySQL has to
scan through all rows in symdata (for each possible value in symbol)!

Note that as long as you use 'todays()', MySQL can't use the date key!

Another problem is that _date.id is not defined exactly as 'symdata.date_id'

Try fixing the fields to have identical types and use the following query:

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

Are you by the way sure that the query shouldn't be:

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

Regards,
Monty