CachedRepository._next_prev_rev is very slow if node_change table is too large on mysql

[Jun Omae]

Changeset view shows next and previous revisions to use node_change table if repository is cached. It it very slow if node_change table on mysql is too large (5,000,000+ rows).

It would improve the performance to use MAX/MIN instead of ORDER BY with LIMIT 1. In other database backends, it would keep the same performance.

direction argument	ORDER BY with LIMIT 1	MAX/MIN
<	38.74 s	0.00s
>	7.81 s	0.00s

mysql> SELECT COUNT(repos) FROM node_change;
+--------------+
| COUNT(repos) |
+--------------+
|      5016444 |
+--------------+
1 row in set (0.72 sec)

mysql> EXPLAIN SELECT MAX(rev) FROM node_change WHERE repos=10 AND rev<'0000000034';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT rev FROM node_change WHERE repos=10 AND rev<'0000000034' ORDER BY rev DESC LIMIT 1;
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys                     | key                       | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | node_change | ref  | PRIMARY,node_change_repos_rev_idx | node_change_repos_rev_idx | 4       | const | 287492 | Using where; Using index |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(rev) FROM node_change WHERE repos=10 AND rev<'0000000034';
+------------+
| MAX(rev)   |
+------------+
| 0000000033 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT rev FROM node_change WHERE repos=10 AND rev<'0000000034' ORDER BY rev DESC LIMIT 1;
+------------+
| rev        |
+------------+
| 0000000033 |
+------------+
1 row in set (38.74 sec)

mysql> EXPLAIN SELECT MIN(rev) FROM node_change WHERE repos=10 AND rev>'0000000034';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT rev FROM node_change WHERE repos=10 AND rev>'0000000034' ORDER BY rev LIMIT 1;
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys                     | key                       | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | node_change | ref  | PRIMARY,node_change_repos_rev_idx | node_change_repos_rev_idx | 4       | const | 287492 | Using where; Using index |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(rev) FROM node_change WHERE repos=10 AND rev>'0000000034';
+------------+
| MIN(rev)   |
+------------+
| 0000000035 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT rev FROM node_change WHERE repos=10 AND rev>'0000000034' ORDER BY rev LIMIT 1;
+------------+
| rev        |
+------------+
| 0000000035 |
+------------+
1 row in set (7.81 sec)

[Jun Omae]

Patch for 0.12-stable