Slow SQL query using Order By

I am far from a SQL guru and I am trying to execute:

SELECT `apps`.* FROM `apps` 
 INNER JOIN `similars` 
 ON (`apps`.id = `similars`.similar_app_id OR `apps`.id = `similars`.app_id) 
WHERE (`similars`.app_id = 542 
 OR `similars`.similar_app_id = 542) 
 AND apps.id <> 542 
ORDER BY field(`similars`.app_id, 542) desc LIMIT 6

The order by makes it 20x slower than without the order by.

explain extended 
SELECT DISTINCT `apps`.* 
  FROM `apps` 
  INNER JOIN `similars` 
    ON (`apps`.id = `similars`.similar_app_id 
    OR `apps`.id = `similars`.app_id) 
  WHERE (`similars`.app_id = 542 
    OR `similars`.similar_app_id = 542) AND apps.id <> 542 
  ORDER BY `similars`.app_id - 542 desc

Gives me :

+----+-------------+----------+-------------+-------------------------------------------------------------------+-----------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------------------------+
| id | select_type | table    | type        | possible_keys                                                     | key                               | key_len | ref  | rows  | filtered | Extra                                                                                        |
+----+-------------+----------+-------------+-----开发者_如何学Python--------------------------------------------------------------+-----------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | similars | index_merge | index_app_id_and_similar_app_id,index_app_id,index_similar_app_id | index_app_id,index_similar_app_id | 5,5     | NULL |   241 |   100.00 | Using union(index_app_id,index_similar_app_id); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | apps     | range       | PRIMARY                                                           | PRIMARY                           | 4       | NULL | 21493 |   100.00 | Using where; Using join buffer                                                               |
+----+-------------+----------+-------------+-------------------------------------------------------------------+-----------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------------------------+

I have tried all combinations of indexes on app_id, similar_app_id and composites of those.

Any tips or tricks?

Thanks

---

regardless of LIMIT sorting by not indexed field - in your case your make calculation on value to sort would always take longer...

I would skip deduction 542 for ordering and add index for app_id

---

One thing you could try is moving the WHERE clause into the JOIN condition. I think mysql will sometimes try to do the join before filtering using the where clause. This is just something you can try and I'm not sure if it will actually help at all.

SELECT `apps`.* FROM `apps` 
 INNER JOIN `similars` 
 ON (`apps`.id = `similars`.similar_app_id OR `apps`.id = `similars`.app_id) 
  AND (
       (`similars`.app_id = 542 OR `similars`.similar_app_id = 542) 
       AND apps.id <> 542
  ) 
ORDER BY field(`similars`.app_id, 542) desc LIMIT 6

---

Since FIELD() is calling a function, MySql cannot use a index or sorting, From the docs:

In some cases, MySQL cannot use indexes to resolve the ORDER BY... You use ORDER BY with an expression that includes terms other than the key column name

Rewrite your ORDER BY to not use any functions, for example if you want the a app_id = 542 to show up on top you can write:

ORDER BY `similars`.app_id = 542 DESC