mysql: how to save ORDER BY after LEFT JOIN without reorder?

Question

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 | <-- WILLIAM IS LAST!
+----+---------+

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 | <-- WILLIAM IN THE MIDDLE!
|  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 :(

Demo: rextester.com/DLLM29415

Demo: http://sqlfiddle.com/#!9/167792/1

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

Answer 1

(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.

Answer 2

Try this:

SELECT
    a.name as `from_name`,
    b.name as `to_name`,
    c.from_id,
    c.to_id
FROM profiles a
LEFT JOIN request_for_friendship c
ON a.id = c.from_id
LEFT JOIN profiles b
ON c.to_id = b.id
GROUP BY a.name,b.name
ORDER BY a.name,b.name;

Or, if you want one row per "from" name:

SELECT
    a.name as `from_name`,
    IFNULL(GROUP_CONCAT(b.name),'-none-') as `to_name`,
    IFNULL(c.from_id,'-none-') as `from_id`,
    IFNULL(GROUP_CONCAT(c.to_id),'-none-') as `to_id`
FROM profiles a
LEFT JOIN request_for_friendship c
ON a.id = c.from_id
LEFT JOIN profiles b
ON c.to_id = b.id
GROUP BY a.name
ORDER BY a.name,b.name

Answer 3

SELECT * 
  FROM profiles p 
  LEFT 
  JOIN request_for_friendship r 
    ON (r.from_id = p.id AND r.to_id = 1) 
    OR (r.from_id = 1 AND r.to_id = p.id)  
 ORDER 
    BY name;
+----+---------+------+---------+-------+
| id | name    | id   | from_id | to_id |
+----+---------+------+---------+-------+
|  8 | CHARLES |    3 |       1 |     8 |
|  6 | DAVID   | NULL |    NULL |  NULL |
|  5 | JAMES   | NULL |    NULL |  NULL |
|  2 | JOHN    |    1 |       1 |     2 |
|  9 | JOSEPH  |    6 |       9 |     1 |
|  4 | MICHAEL |    5 |       4 |     1 |
|  7 | RICHARD | NULL |    NULL |  NULL |
|  3 | ROBERT  |    2 |       1 |     3 |
| 10 | THOMAS  | NULL |    NULL |  NULL |
|  1 | WILLIAM | NULL |    NULL |  NULL |
+----+---------+------+---------+-------+
10 rows in set (0.02 sec)

mysql>

Answer 4

I know this question is a couple of years old, but I didn't find this possible solution already offered. This is the solution that worked best for me to keep the subquery results in the correct order.

Consider adding a "row_number" to your subquery. Then use ORDER BY on row_number.

This explains how to add the row_number: select increment counter in mysql

In my case, I have an unknown number of possible rows in a hierarchical recursive query that I need to keep the order results of the subquery to remain the same in the outer query.

This is my query:

SELECT l.row_number, l.userid, l.child, p.id, p.username
FROM (

    SELECT  @rownum := @rownum + 1 AS row_number, u.parent AS userid, _id  AS child 
                FROM (
                    SELECT  @r AS _id, (SELECT  @r := parent FROM new_clean WHERE userid = _id) AS parent
                                FROM (SELECT @r := ?) AS vars, new_clean h
                                WHERE   @r <> 0 
                    ) u
                CROSS JOIN (SELECT @rownum := 0) r
                WHERE u.parent <> 0                     
    ) l 

        LEFT JOIN profile p ON p.userid = l.userid
        ORDER BY row_number