Mysql using filesort even when using index and only one row

Question

I have two tables, app and pricehistory there is a primary index id on app which is an int on pricehistory i have two fields id_app (int), price(float) and dateup (date) and an unique index on "id_app, dateup"

i'm trying to get the latest (of date) price of an app :

select app.id,
       (  select price 
          from pricehistory 
          where id_app=app.id 
          order by dateup desc limit 1) 
from app 
where id=147

the explain select is kind of weird because it return 1 row but it still makes a filesort :

id  select_type        table        type   possible_keys            key      key_len   ref     rows  Extra      
1   PRIMARY            app          const  PRIMARY                  PRIMARY     4      const      1   
2   DEPENDENT SUBQUERY pricehistory ref    id_app,id_app_2,id_app_3 id_app      4      const      1  Using where; Using filesort

why does it need to filesort when there is only 1 row ? and why it's file sorting when i'm indexing all it need (id_app and dateup)

app has 1 million rows and i'm using innodb

edit: a sql fiddle explaining the problem:

http://sqlfiddle.com/#!2/085027/1

edit3 : a new fiddle with another request with the same problem : http://sqlfiddle.com/#!2/f7682/6

edit4: this fiddle ( http://sqlfiddle.com/#!2/2785c/2 ) shows that the query proposed doesn't work because it select all the data from pricehistory just to fetch the ones i want

Answer 1

Here's a quick rule of thumb for which order columns should go in an index:

1. Columns referenced in the WHERE clause with an equality condition (=).

2. Choose one of:

   a. Columns referenced in the ORDER BY clause.

   b. Columns referenced in a GROUP BY clause.

   c. Columns referenced in the WHERE clause with a range condition (!=, >, <, IN, BETWEEN, IS [NOT] NULL).

3. Columns referenced in the SELECT-list.

See How to Design Indexes, Really.

In this case, I was able to remove the filesort with this index:

mysql> alter table pricehistory add  key bk1 (id_app, dateup, price_fr);

And here's the EXPLAIN, showing no filesort, and the improvement of "Using index":

mysql> explain select price_fr from pricehistory where id_app=1 order by dateup desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pricehistory
         type: ref
possible_keys: bk1
          key: bk1
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where; Using index

You can make this index UNIQUE if you want to.

I had to drop the other unique keys, to avoid confusing the optimizer.