MySQL db is using "Using where; Using temporary; Using filesort" when sorting by date

I have a database with a bunch of records and when I load up the page with the following SQL its really slow.

SELECT goal.title, max(updates.date_updated) as update_sort 
FROM `goal` 
LEFT OUTER JOIN `update` `updates` ON (`updates`.`goal_id`=`goal`.`goal_id`) 
WHERE (goal.private=0) 
GROUP BY updates.goal_id 
ORDER BY update_sort desc 
LIMIT 12

When I do an explain it says its not using any keys and that its searching every row. Also telling me its using "Using where; Using temporary; Using filesort".

Any help is much appreciated

Thanks

It needs to be grouped by goal_id because of the MAX() in the select is returning only one row.

What i'm trying to do is return the MAX date_updated row from the updates table for each goal and then sort it by that column.

Cur开发者_高级运维rent indices are on goal.private and update.goal_id

output of EXPLAIN (can't upload images so have to put it here sorry if it isnt clear:

id  select_type     table   type    possible_keys   key         key_len     ref           rows  Extra
1   SIMPLE          goal    ref     private         private     1           const         27    Using temporary; Using filesort
1   SIMPLE          updates ref     goal_id         goal_id     4           goal.goal_id  1     

SELECT  u.date_updated, g.title
FROM    updates u
JOIN    goal g
ON      g.goal_id = u.goal_id
WHERE   u.id = 
        (
        SELECT  ui.id
        FROM    updates ui
        WHERE   ui.goal_id = u.goal_id
        ORDER BY
                ui.goal_id, ui.date_updated, ui.id
        LIMIT 1
        )
        AND g.private = 0
ORDER BY
        u.date_update, u.id
LIMIT 12

Create two indexes on updates for the query to work fast:

updates (goal_id, date_updated, id)
updates (date_updated, id)

My guess is that the MAX() function causes this behaviour. It needs to look at every record to decide what rows to select. What kind of grouping are you trying to accomplish?