Currently the workbench_path_revision database table does not have any indexes. Adding an index to this table could help improve the performance of functions that make calls to this table for path revision information.

Comments

zd123’s picture

zd123’s picture

StatusFileSize
new998 bytes

This patch adds an index to {workbench_path_revision}.vid as well.

zd123’s picture

StatusFileSize
new1003 bytes
zd123’s picture

StatusFileSize
new1008 bytes
linvald’s picture

We also need an index on alias. This is important for large sets of data.

mysql> explain  SELECT workbench_path_revision.nid FROM workbench_path_revision JOIN node ON workbench_path_revision.nid = node.nid AND workbench_path_revision.vid = node.vid WHERE workbench_path_revision.alias = 'some.html';
+----+-------------+-------------------------+-------+-----------------+---------+---------+-----------------------------------------------+--------+-------------+
| id | select_type | table                   | type  | possible_keys   | key     | key_len | ref                                           | rows   | Extra       |
+----+-------------+-------------------------+-------+-----------------+---------+---------+-----------------------------------------------+--------+-------------+
|  1 | SIMPLE      | node                    | index | PRIMARY,vid     | vid     | 5       | NULL                                          | 574224 | Using index |
|  1 | SIMPLE      | workbench_path_revision | ref   | PRIMARY,nid,vid | PRIMARY | 8       | some.node.nid,some.node.vid |      1 | Using where |
+----+-------------+-------------------------+-------+-----------------+---------+---------+-----------------------------------------------+--------+-------------+

mysql> alter table workbench_path_revision add index (alias);

mysql> explain SELECT workbench_path_revision.nid FROM workbench_path_revision WHERE workbench_path_revision.alias = 'data.html';
+----+-------------+-------------------------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table                   | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------------------------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | workbench_path_revision | ref  | alias         | alias | 767     | const |    1 | Using where; Using index |
+----+-------------+-------------------------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
linvald’s picture

linvald’s picture

Status: Active » Needs review