Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL

[Peter Brawley]

As Explain Extended shows, the query engine can't find keys to use, so it's having to scan both tables, write all that to temp tables, then sort that on disk.

Most of the query task is in fact_amounts. Give the query engine keys to use, ... KEY (symbolID,tier,amount), KEY (exec_date,symbolID) ..., then the aggregating query looks like this:

explain extended
select symbolID, tier, sum(amount)
from fact_amounts 
group by symbolID, tier;
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table        | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | fact_amounts | index | NULL          | SymbolID | 18      | NULL |    3 |   100.00 | Using index |
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-------------+

(Use BBCode code tags to format code on this forum.)

Now try a similar trick for the lookup table, change its key to (symbolID,symbol), and write the query thus:

explain extended
select a.symbol, b.tier, b.total
from (
  select symbolID, tier, sum(amount) as total
  from fact_amounts 
  group by symbolID, tier
) b
join dimension_symbol a using(symbolID);
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table        | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | PRIMARY     | a            | index | PRIMARY       | PRIMARY  | 12      | NULL |    3 |   100.00 | Using index                                               |
|  1 | PRIMARY     | <derived2>   | ALL   | NULL          | NULL     | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (BNL, incremental buffers) |
|  2 | DERIVED     | fact_amounts | index | NULL          | SymbolID | 18      | NULL |    3 |   100.00 | Using index                                               |
+----+-------------+--------------+-------+---------------+----------+---------+------+------+----------+-----------------------------------------------------------+

How does that perform?

PB
http://www.artfulsoftware.com