Slow SQL query using Order By

Question

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                                                                                        |
+----+-------------+----------+-------------+-------------------------------------------------------------------+-----------------------------------+---------+------+-------+----------+----------------------------------------------------------------------------------------------+
|  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

What are you using the `field()` function for in the first query? — dcrosta, Mar 21 '11 at 03:10

bensiu · Answer 1 · 2011-03-21T02:15:42.843

0

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

edited Mar 21 '11 at 02:15

answered Mar 21 '11 at 02:03

bensiu

24,660
56
77
117

Hi Bensiu, thanks for the answer, unfortunately I need the order otherwise it runs fast :) I do have an index on app_id (index_app_id in the explain) – Stuart Mar 21 '11 at 02:14

Joe Phillips · Answer 2 · 2011-03-21T02:31:06.077

0

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

edited Mar 21 '11 at 02:31

answered Mar 21 '11 at 02:09

Joe Phillips

49,743
32
103
159

Hi Joe, thanks for the answer! Do you mean make the WHERE an AND on the join? If so I tried and and it seems about the same :| – Stuart Mar 21 '11 at 02:17
Thanks Joe, that does speed things up by a factor of 2, I'll see if I can tweak it any futher – Stuart Mar 21 '11 at 03:34

score 0 · Answer 3 · answered Mar 21 '11 at 04:20

0

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

answered Mar 21 '11 at 04:20

The Scrum Meister

29,681
8
66
64

doesn't seem to help unfortunately :( – Stuart Mar 21 '11 at 05:33

Slow SQL query using Order By

3 Answers3