Mysql subselect extremely slow (doesn't seem to be using indexes)

This query doesn't complete in a reasonable amount of time:

mysql> select * from prices where symbol='GOOG' and date in
(select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001
 group by yearweek(date));

'prices' is keyed off id, and has a secondary unique index of (symbol, date):

mysql> show index from prices;
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| prices |          0 | PRIMARY         |            1 | id          | A         |      468915 |     NULL | NULL   |      | BTREE      |         |
| prices |          0 | SECONDARY_INDEX |            1 | date        | A         |       10905 |     NULL | NULL   | YES  | BTREE      |         |
| prices |          0 | SECONDARY_INDEX |            2 | symbol      | A   开发者_Go百科       |      468915 |     NULL | NULL   | YES  | BTREE      |         |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

From looking at the EXPLAIN output, it seems as though MySql is not using the index on (date, symbol). Does the combination of WHERE symbol='GOOG' and date IN (...)) require a full table scan and not use the secondary unique index?

mysql> EXPLAIN select * from prices where symbol='GOOG' and date in (select max(date) from prices where symbol='GOOG' and yearweek(date) > 201001 group by yearweek(date));
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type        | table  | type  | possible_keys | key             | key_len | ref  | rows   | Extra                                                     |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
|  1 | PRIMARY            | prices | ALL   | NULL          | NULL            | NULL    | NULL | 468915 | Using where                                               |
|  2 | DEPENDENT SUBQUERY | prices | index | NULL          | SECONDARY_INDEX | 17      | NULL | 468915 | Using where; Using index; Using temporary; Using filesort |
+----+--------------------+--------+-------+---------------+-----------------+---------+------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Try a JOIN:

SELECT 
    prices.* 
FROM 
    prices 
        JOIN
            (SELECT MAX(date) AS maxdate FROM prices WHERE symbol='GOOG' AND yearweek(date) > 201001 GROUP BY yearweek(date)) 
        AS sub ON prices.date = sub.maxdate
WHERE 
    symbol='GOOG' ;

But the yearweek() will still be a problem, MySQL can't use an index on this one. Precalculate this value (using a trigger) , store it in an extra column and index this column, might help.

Can you not just do this?

  SELECT *
    FROM prices
   WHERE symbol = 'GOOG'
     AND date >  '2010-12-18 23:59:59'
ORDER BY date DESC
GROUP BY yearweek(date)
   LIMIT 1

The yearweek() disables the index on column date.

Try changing it to:

and date > date'2010-01-09'

...where 2010-01-09 is the last day in week 201001. Beware of timestamps in the column though.