mysql 5.5 join排序_mysql：如何在LEFT JOIN之后保存ORDER BY而不重新排序？

I've two table:

1) profiles

+----+---------+

| id | name |

+----+---------+

| 1 | WILLIAM |

| 2 | JOHN |

| 3 | ROBERT |

| 4 | MICHAEL |

| 5 | JAMES |

| 6 | DAVID |

| 7 | RICHARD |

| 8 | CHARLES |

| 9 | JOSEPH |

| 10 | THOMAS |

+----+---------+

2) request_for_friendship

+----+---------+-------+

| id | from_id | to_id |

+----+---------+-------+

| 1 | 1 | 2 |

| 2 | 1 | 3 |

| 3 | 1 | 8 |

| 5 | 4 | 1 |

| 6 | 9 | 1 |

+----+---------+-------+

I need to get all profiles with some sorting and join it with request_for_friendship

For example, get all users with some sorting:

mysql> SELECT *

-> FROM profiles

-> ORDER BY name ASC;

+----+---------+

| id | name |

+----+---------+

| 8 | CHARLES |

| 6 | DAVID |

| 5 | JAMES |

| 2 | JOHN |

| 9 | JOSEPH |

| 4 | MICHAEL |

| 7 | RICHARD |

| 3 | ROBERT |

| 10 | THOMAS |

| 1 | WILLIAM |

+----+---------+

Everything looks good, sorting is present. After that I join with request_for_friendship and my sotring will breaks:

mysql> SELECT * FROM

-> (

-> SELECT *

-> FROM profiles

-> ORDER BY name ASC

-> ) as users

-> LEFT JOIN request_for_friendship

-> AS request_for_friendship_copy

-> ON

-> (

-> request_for_friendship_copy.from_id = 1

-> AND

-> request_for_friendship_copy.to_id = users.id

-> )

-> OR

-> (

-> request_for_friendship_copy.from_id = users.id

-> AND

-> request_for_friendship_copy.to_id = 1

-> );

+----+---------+------+---------+-------+

| id | name | id | from_id | to_id |

+----+---------+------+---------+-------+

| 2 | JOHN | 1 | 1 | 2 |

| 3 | ROBERT | 2 | 1 | 3 |

| 8 | CHARLES | 3 | 1 | 8 |

| 4 | MICHAEL | 5 | 4 | 1 |

| 9 | JOSEPH | 6 | 9 | 1 |

| 1 | WILLIAM | NULL | NULL | NULL |

| 5 | JAMES | NULL | NULL | NULL |

| 6 | DAVID | NULL | NULL | NULL |

| 7 | RICHARD | NULL | NULL | NULL |

| 10 | THOMAS | NULL | NULL | NULL |

+----+---------+------+---------+-------+

How to JOIN LEFT with original sorting saving?

I can't sort result after JOIN LEFT besause when I do ORDER BY before JOIN it takes ~0.02s in my db (~1 000 000 users) but when I do ORDER BY after JOIN it takes ~3.2s, it's very big time :(

In sqlfiddle order is saved! But how? MySQL 5.6 saved order?

解决方案

(Explaining the loss of ORDER BY)

The SQL standard essentially says that a subquery is an unordered set of rows. This implies that the Optimizer is free to ignore the ORDER BY in the 'derived' table: FROM ( SELECT ... ORDER BY ). In "recent" versions of MySQL and MariaDB, such ORDER BYs are being dropped. There are other cases where ORDER BY is ignored.

In some situations (not sure about this one), adding a LIMIT 99999999 (big number) after the ORDER BY tricks the Optimizer into doing the ORDER BY. However, it is still free to ignore the "order" later.

A general rule for MySQL: Avoid subqueries. (There are cases where subqueries are faster, but not yours.)

A strong rule: You must have an ORDER BY on the outermost if you want the results to be sorted.

If you had added LIMIT 3 to the derived table in your first query, you would get only CHARLES, DAVID, JAMES, but not necessarily in that order. That is, you would need two ORDER BYs - one in the derived table, one at the very end.