Add (or use existing) indexes for the Webform results page

I'm assuming you mean the table "webform_submissions", not "webform_get_submissions"? There's already an "sid_nid" index on the table, I'd assume that would do the job already. But in any case, a bit more information is needed on this suggestion.

Oh, and the primary key (auto-increment) is already on SID on the "webform_submissions" table. So there should be an index on it already there too.

I simply added a single index on field sid of table webform_submitted_data and this solved the problem.

Hmm, there's already an index on "webform_submitted_data" table for "sid_nid", so that should provide an adequate index for queries that use the SID alone. I'll do some more looking into this.

So loading the node/x/webform-results page there are 3 queries being executed.

1. A count of how many submissions there are total.
2. A selection of submission IDs for the first page.
3. Loading of the entirety of the submissions on the first page.

The first query I couldn't find any issue with. It was fast pretty much no matter what.

The second query I found that it was using the dreaded "filesort" method of sorting submissions.Current selecting from webform_submissions table (0.7ms in my testing):

mysql> EXPLAIN SELECT ws.sid AS sid FROM webform_submissions ws WHERE (nid = '52') ORDER BY sid DESC LIMIT 50 OFFSET 0;
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                    |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | ws    | ref  | nid_uid_sid   | nid_uid_sid | 4       | const |   24 | Using where; Using index; Using filesort |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)

I was able to speed this query up a bit by adding a new "nid_sid" index on webform_submissions (0.6ms in my testing):

mysql> EXPLAIN SELECT ws.sid AS sid FROM webform_submissions ws WHERE (nid = '52') ORDER BY sid DESC LIMIT 50 OFFSET 0;
+----+-------------+-------+------+---------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys       | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | ws    | ref  | nid_uid_sid,nid_sid | nid_sid | 4       | const |   24 | Using where; Using index |
+----+-------------+-------+------+---------------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

The third query however was were real performance problems may be encountered. It was both using a temporary table and a filesort. The double-whammy-worse-possible-scenario.

Current loading of actual submissions from webform_submitted_data table (3.6ms):

mysql> EXPLAIN SELECT s.*, sd.cid AS cid, sd.no AS no, sd.data AS data, u.name AS name FROM webform_submissions s LEFT OUTER JOIN webform_submitted_data sd ON sd.sid = s.sid LEFT OUTER JOIN users u ON u.uid = s.uid WHERE (s.sid IN ('....')) ORDER BY sd.sid ASC, sd.cid ASC, sd.no ASC;
+----+-------------+-------+--------+-----------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref           | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+---------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | ALL    | PRIMARY,sid_nid | NULL    | NULL    | NULL          |  123 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | sd    | ref    | sid_nid         | sid_nid | 4       | drupal7.s.sid |    5 |                                              |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY         | PRIMARY | 4       | drupal7.s.uid |    1 |                                              |
+----+-------------+-------+--------+-----------------+---------+---------+---------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

Fortunately fixing this problem was trivial. We already have an index for "nid_sid_cid_no", all that needed to be added was "nid" to the select query and everything went much faster (2.6ms):

mysql> EXPLAIN SELECT s.*, sd.cid AS cid, sd.no AS no, sd.data AS data, u.name AS name FROM webform_submissions s LEFT OUTER JOIN webform_submitted_data sd ON sd.sid = s.sid LEFT OUTER JOIN users u ON u.uid = s.uid WHERE (s.sid IN ('....')) AND (sd.nid = '52') ORDER BY sd.sid ASC, sd.cid ASC, sd.no ASC;
+----+-------------+-------+--------+---------------------+---------+---------+----------------+------+-------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref            | rows | Extra       |
+----+-------------+-------+--------+---------------------+---------+---------+----------------+------+-------------+
|  1 | SIMPLE      | sd    | range  | PRIMARY,nid,sid_nid | PRIMARY | 8       | NULL           |   56 | Using where |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY,sid_nid     | PRIMARY | 4       | drupal7.sd.sid |    1 |             |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY             | PRIMARY | 4       | drupal7.s.uid  |    1 |             |
+----+-------------+-------+--------+---------------------+---------+---------+----------------+------+-------------+
3 rows in set (0.01 sec)

Though on my machine these tables are really quite small. The benefit would be much greater for larger tables such as yours.

Unfortunately in this testing I couldn't actually find any way that adding a simple index on "sid" would have helped. Like I said, we already have an index on sid (sid_nid) that should be doing the job.

On my D6 site I've gotten slightly different numbers, probably due to the table being a bit larger in this case. According to devel.module logging on the node/x/webform-results page:

Before changes: 18.53ms webform_get_submissions()
After changes: 7.72ms webform_get_submissions()

So that's roughly a 2.5x increase, which is pretty good. I don't have any test database that are anywhere near a million rows right now (though I've heard multiple reports that it works fine with tables that large), so I'd appreciate it if you could test out these changes and see if you get any improvements. Preferably, you'd remove the indexes you've added manually to make it so that you're testing a stock webform install, but I imagine this would give you a benefit even if you just applied it on top of your existing site.

I've committed these changes as it's a pretty big performance boost in my testing. Please let me know if you experience any problems or find this doesn't have an increase in performance for you.