panelizer_entity select query optimizations

Committed and pushed to both branches.

On large sites, this query is still not very performant. Working with Drumm, it appears that this original patch is flawed here, and instead an index to revision_id should be added.

Some results below with MYSQL explain and index.

Without an index, the original code. Notice that it returns more rows on a large site. (which is probably why the query is slow)

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY       | PRIMARY | 386     | const |   35 |   100.00 | Using where |
+----+-------------+------------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

After the original code patch/commit in this issue. key_len is slightly longer due to fulltext search of the entity_type and union of the referenced primary keys. However, it is returning 1 row, which is expected and will be faster than the first query.

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND entity_id IN (2202249) AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table            | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY       | PRIMARY | 394     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+------------------+------+---------------+---------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

These next two are like the ones above, but after adding a new index called revision which puts together revision_id and entity_type. Not much of a performance/calls change vs the current patch.

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table            | type | possible_keys       | key         | key_len | ref         | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------------+-------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY,revision_id | revision_id | 390     | const,const |    1 |   100.00 | Using where |
+----+-------------+------------------+------+---------------------+-------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND entity_id IN (2202249) AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table            | type | possible_keys       | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY,revision_id | PRIMARY | 394     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Change the revision index to just use revision_id. Much shorter key_len and in theory this should be faster.

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table            | type | possible_keys       | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY,revision_id | revision_id | 4       | const |    1 |   100.00 | Using where |
+----+-------------+------------------+------+---------------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

And looking at the current code, we should revert back to the original code, because it is faster with the index.

mysql> EXPLAIN EXTENDED SELECT * FROM panelizer_entity WHERE entity_type = 'node' AND entity_id IN (2202249) AND revision_id IN (9044167);
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table            | type | possible_keys       | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | panelizer_entity | ref  | PRIMARY,revision_id | PRIMARY | 394     | const,const,const |    1 |   100.00 | Using where |
+----+-------------+------------------+------+---------------------+---------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Removing the original query this issue fixed. This includes the patch in #3

This is failing due to the fact that our patch relies on #2457113: Better Revision Handling inside and outside of Workbench Moderation

Here's a patch for dev that should work.

The patch will need to be rerolled as panelizer_schema() was rewritten.

Rerolled.

Duh, Damien.

Committed. Thanks!