Optimize MySql With Order By, Group By And One Million Rows

TestAppData:

instance_id INT

product_id SMALLINT

installation_id INT

data_id SMALLINT

data_value_str varchar(255)

data_value_num INT

timestamp

Index (installation_id)

Index (installation_id, data_value_num)

TestAppInstallations:

prod开发者_StackOverflow中文版uct_id SMALLINT

installation_id INT

Primary(product_id, installation_id)

   SELECT TestAppInstallations.installation_id, 
          TestAppData.data_value_num AS num1
     FROM TestAppData
LEFT JOIN TestAppInstallations ON TestAppInstallations.installation_id = TestAppData.installation_id
                              AND TestAppData.data_id =1
 GROUP BY TestAppInstallations.installation_id
 ORDER BY TestAppData.data_value_num DESC 
    LIMIT 0 , 20

Explain output

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
------------------------------------------------------------------------------
1 SIMPLE TestAppData ALL NULL NULL NULL NULL 360000 Using temporary; Using filesort 
1 SIMPLE TestAppInstallations index NULL PRIMARY 6 NULL 20000 Using index 

With the above tables, mysql query and explain output. The query is super slow, as in slow enough that I didn't let it finish. There are over 1 million rows.

I've tried indexes on installation_id and data_id, but that didn't help. I admit that I don't understand too clearly what kind of index I would need.

Is there anything else I can do to make this workable?

Edit: Sorry, 1,000,000 rows, not tables.

I reversed the JOIN order and the query went lightning fast. So instead of joining the data table to the installation table, now the reverse.

SELECT TestAppInstallations.installation_id, TestAppData.data_value_num AS num1
FROM TestAppInstallations
LEFT JOIN TestAppData ON TestAppInstallations.installation_id = TestAppData.installation_id
AND TestAppData.data_id =1
GROUP BY TestAppInstallations.installation_id
ORDER BY TestAppData.data_value_num DESC 
LIMIT 0 , 20