r74298 MediaWiki - Code Review archive

I think you want 'ORDER BY wl_namespace,wl_title' here (or perhaps 'ORDER BY wl_user,wl_namespace,wl_title' if optimizers get confused).

Is there actually a need for the conditional?

I seem to recall MySQL folk complaining when I tried out a direct ORDER BY. This was some years ago now, so I may be wrong. Certainly a direct ORDER BY gets my vote.

On my local MySQL test, EXPLAIN tosses in a 'using where' when using the order by (?) but otherwise doesn't seem to change. I'd defer to scary Domas if he can point out dangers though. :D

Main thing is to make sure that the ordering is correct and doesn't introduce a filesort; ordering just on wl_title will give a different ordering than expected, which doesn't match up to available indexes.

mysql> DESCRIBE SELECT wl_namespace, wl_title, page_id, page_len, page_is_redirect, page_latest FROM mw_watchlist LEFT JOIN mw_page ON ( wl_namespace = page_namespace AND wl_title = page_title ) WHERE wl_user = 1;
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+-------------+
| id | select_type | table        | type   | possible_keys | key        | key_len | ref                                                           | rows | Extra       |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+-------------+
|  1 | SIMPLE      | mw_watchlist | ref    | wl_user       | wl_user    | 4       | const                                                         |    6 | Using index |
|  1 | SIMPLE      | mw_page      | eq_ref | name_title    | name_title | 261     | wikidb.mw_watchlist.wl_namespace,wikidb.mw_watchlist.wl_title |    1 |             |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE SELECT wl_namespace, wl_title, page_id, page_len, page_is_redirect, page_latest FROM mw_watchlist LEFT JOIN mw_page ON ( wl_namespace = page_namespace AND wl_title = page_title ) WHERE wl_user = 1 ORDER BY wl_title;
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+------------------------------------------+
| id | select_type | table        | type   | possible_keys | key        | key_len | ref                                                           | rows | Extra                                    |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+------------------------------------------+
|  1 | SIMPLE      | mw_watchlist | ref    | wl_user       | wl_user    | 4       | const                                                         |    6 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | mw_page      | eq_ref | name_title    | name_title | 261     | wikidb.mw_watchlist.wl_namespace,wikidb.mw_watchlist.wl_title |    1 |                                          |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+------------------------------------------+
2 rows in set (0.00 sec)


mysql> DESCRIBE SELECT wl_namespace, wl_title, page_id, page_len, page_is_redirect, page_latest FROM mw_watchlist LEFT JOIN mw_page ON ( wl_namespace = page_namespace AND wl_title = page_title ) WHERE wl_user = 1 ORDER BY wl_namespace, wl_title;
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
| id | select_type | table        | type   | possible_keys | key        | key_len | ref                                                           | rows | Extra                    |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
|  1 | SIMPLE      | mw_watchlist | ref    | wl_user       | wl_user    | 4       | const                                                         |    6 | Using where; Using index |
|  1 | SIMPLE      | mw_page      | eq_ref | name_title    | name_title | 261     | wikidb.mw_watchlist.wl_namespace,wikidb.mw_watchlist.wl_title |    1 |                          |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
2 rows in set (0.00 sec)


mysql> DESCRIBE SELECT wl_namespace, wl_title, page_id, page_len, page_is_redirect, page_latest FROM mw_watchlist LEFT JOIN mw_page ON ( wl_namespace = page_namespace AND wl_title = page_title ) WHERE wl_user = 1 ORDER BY wl_user, wl_namespace, wl_title;
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
| id | select_type | table        | type   | possible_keys | key        | key_len | ref                                                           | rows | Extra                    |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
|  1 | SIMPLE      | mw_watchlist | ref    | wl_user       | wl_user    | 4       | const                                                         |    6 | Using where; Using index |
|  1 | SIMPLE      | mw_page      | eq_ref | name_title    | name_title | 261     | wikidb.mw_watchlist.wl_namespace,wikidb.mw_watchlist.wl_title |    1 |                          |
+----+-------------+--------------+--------+---------------+------------+---------+---------------------------------------------------------------+------+--------------------------+
2 rows in set (0.00 sec)

Using no order by is better than order by just wl_title (it starts filesorting) on my dev wiki install..

Trivially easy to fix. Anyone any preferences?

ORDER BY wl_title is bad in MySQL because there is no index on wl_title, just on (wl_user, wl_namespace, wl_title). However, $dbr->implicitOrderBy() returns true on MySQL (and on SQLite), so the bad ORDER BY isn't actually added on MySQL installs, just on Postgres and a few unsupported backends (MSSQL, Oracle, DB2).

If Postgres will work with ORDER BY wl_namespace, wl_title (which is what you should actually use to get the same results as MySQL's implicit ordering), just add that unconditionally.

Can someone with Postgres check on this?

Postgres is fine with that order by

The one set by this revision? Does it not filesort or anything similar?

Postgres is fine with 'ORDER BY wl_namespace,wl_title'

Thanks. Added that in r79864