This is a spin-off from #1087864: Trying to get property of non-object in webform_results_submissions(), where the performance of the query in webform_get_submissions() was analyzed. The query in that function is specifically crafted to avoid sorting and filtering on multiple tables. However when running EXPLAIN on the queries, it's apparent that MySQL is still needing to sort the entries manually:

mysql> EXPLAIN SELECT s.*, sd.cid, sd.no, sd.data, u.name FROM webform_submitted_data sd LEFT JOIN webform_submissions s ON s.sid = sd.sid LEFT JOIN users u ON u.uid = s.uid WHERE sd.sid IN (10, 11, 12, 13, 14, 15, 16, 17 , 18, 19, 20) 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  | sid_nid         | sid_nid | 4       | NULL           |  121 | Using where; Using filesort |
|  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.00 sec)

The "Using filesort" indicates MySQL had to manually sort the result without an index.

If we were to add an index for (sid, cid, no), it would eliminate the manual sort:

mysql> EXPLAIN SELECT s.*, sd.cid, sd.no, sd.data, u.name FROM webform_submitted_data sd RIGHT JOIN webform_submissions s ON s.sid = sd.sid LEFT JOIN users u ON u.uid = s.uid WHERE s.sid IN (10, 11, 12, 13, 14, 15, 16, 17 , 18, 19, 20) 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     | range  | PRIMARY,sid_nid | PRIMARY | 4       | NULL          |   11 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | sd    | ref    | sid_nid         | sid_nid | 4       | drupal7.s.sid |    1 |                                              |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY         | PRIMARY | 4       | drupal7.s.uid |    1 |                                              |
+----+-------------+-------+--------+-----------------+---------+---------+---------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

Considering this is the most intensive query in the whole of Webform, the additional optimization could yield significant gains in query speed.

Comments

quicksketch’s picture

quicksketch
he/him
commented
Title: Add an index to optimize webform_get_submissions() » Add additional index to optimize webform_get_submissions()
Status: Active » Closed (works as designed)

Actually it looks like for most queries (which are on a particular NID anyway), we're already using the PRIMARY key. There's even a comment to this effect:

  // By adding the NID to this query we allow MySQL to use the primary key on
  // in webform_submitted_data for sorting (nid_sid_cid_no).
  if (isset($filters['nid'])) {
    $query->condition('sd.nid', $filters['nid']);
  }

We're already adding NID in every place in webform already, so I don't think that this additional index is really necessary. False alarm. :)