unique validation terribly slow on large databases (due to missing index)

The only downside to this is that the index will be huge, since its indexing every component of every submission of every webform.

I'm not suggesting that you not have an index. There is nothing to prevent you from adding the index on your own (either manually or in a custom module). What I'm wondering is if all 400,000-ish webform installations need the index.

EDIT: BTW, indices on ids will be small. An index on all the data of every component of every webform won't.

Increasing the size of the site's indices by 20+% solely for the case in which someone might add unique validation doesn't sound like a good solution to me.

I think what we need is either an indication to the site builder that an index needs to be added if unique validation is slow and/or a configuration option to do it. I would imagine that adding the index to a large table might take quite a bit of time.

I'm not saying that your installation shouldn't have an index. Clearly it should.

Are you saying that every webform installation should have an index even though the vast majority (probably > 99%) don't need or want it?

But the cost of indices in small sites is correspondingly low.

So what are the available options?

1) Add an index to every site (a la the patch above)

2) Add a webform admin config option to add the index, explaining that it is useful to speed up unique validation.

3) Add the index automatically when the first webform component with unique validation is added to the site.

4) Remove unique validation from webform and add it to a trivial helper module, which would also add/remove the index upon installation/uninstallation.

5) Add a message when unique validation is turned on (and there isn't yet an index) saying that it may be slow unless an index is added.

5a) ... and provide a link which adds the index.

Any other ideas?

@quicksketch -- whatchathink?

@torotil -- how long did the index take to create on your server with your dataset? Could it reasonably be done via the UI (i.e. not in an update routine off-line) after there is a large amount of data?

BTW, I'm only commenting on this because I'm willing to commit a tested patch that I think is reasonable and that I'm 99% sure quicksketch would support.

Well we could try increasing the PHP time-out before adding the index which might (or might not) help. Alternatively, it could be schedule to run on the next cron job.

@torotil - you're assuming option 1. Options 2, 3, and 5a would not happen via drush.

Speaking of drush....

Option 6: Add a trivial drush command to add the index and reference it in the on-line documentation somewhere and/or in the #description for unique validation.

Hi guys, thanks for the great discussion in here. I learned a few things just reading through it. I didn't know you could have an index on only part of a database column.

Whenever possible, I prefer to make a decision rather than making a new option. Let's pick the best solution that helps the most sites and hurts the fewest.

From reading over the options, I think @torotil's original suggestion makes a lot of sense. The index only lists the first 256 bytes, and it doesn't combine with any other columns, making for an overall inexpensive index. 40s for adding an index on 2.5 million rows doesn't seem like a problem to me. Most PHP installations of that size need to have a timeout at 60s or more anyway. If we're paranoid about it, we can use ini_set() during the update to increase the timeout time. This will very slightly increase the time it takes to write a new submission, but it makes submission time more consistent. If unique validation is enabled and that suddenly makes submissions take 5-10x longer, that's a problem. I like this solution that very slightly increases the writing time, and unique validation doesn't carry such a burden.

I'm fine with this patch's current approach (just adding an index on the first 256 bytes). However, I'd like to know that we're doing this correctly. My optimization experience would tell me that indexing "data" alone would be good but an index of nid, cid, data would be ideal, since that's what the webform_validate_unique() query is against. That way you'd get a fully indexed query instead of only getting an index on data.

On my localhost, running EXPLAIN on the query made no difference before or after adding the index. In both cases the query used the PRIMARY key (which is the nid, sid, cid, no index). The index used probably depends on the amount of data you have in the table however. On my localhost there are only 10K rows, as opposed to several million.

Here's the EXPLAIN with the new "data" index. Note that the primary key is still used:

mysql> EXPLAIN SELECT sid FROM webform_submitted_data WHERE nid = 119 AND cid = 1 AND data = 'Test' LIMIT 0,1;
+----+-------------+------------------------+-------+------------------+---------+---------+------+------+-------------+
| id | select_type | table                  | type  | possible_keys    | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------------------+-------+------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | webform_submitted_data | range | PRIMARY,nid,data | PRIMARY | 4       | NULL |  543 | Using where |
+----+-------------+------------------------+-------+------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Here's the EXPLAIN with an index for nid_cid_data added:

mysql> EXPLAIN SELECT sid FROM webform_submitted_data WHERE nid = 119 AND cid = 1 AND data = 'Test' LIMIT 0,1;
+----+-------------+------------------------+------+--------------------------+--------------+---------+-------------------+------+-------------+
| id | select_type | table                  | type | possible_keys            | key          | key_len | ref               | rows | Extra       |
+----+-------------+------------------------+------+--------------------------+--------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | webform_submitted_data | ref  | PRIMARY,nid,nid_cid_data | nid_cid_data | 773     | const,const,const |   11 | Using where |
+----+-------------+------------------------+------+--------------------------+--------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

I'm not sure about the wisdom of this longer key vs. one just on the data column yet. I'll need to do some more testing.

Well, oddly after adding the nid_cid_data index, queries seem to actually take *longer*. Before adding an index, queries took roughly 9ms. After adding the nid_cid_data index, queries took 12ms (this is out of 3 tests, restarting MySQL between tests to clear caches). Adding the data index made no difference for me, since per the EXPLAIN output above, the "data" index doesn't get used on my table anyway.

Of course this is a matter of ~3ms, so I don't think this is important on a database of my size. We'd need a larger test base to determine a significant difference.

@torotil: Could you include the EXPLAIN output on your site for a sample query before/after adding the index? Does a more complete index help or hurt in your case?

256 is to long even InnoDB table the limit is 767 bytes (see http://dev.mysql.com/doc/refman/5.1/en/create-index.html)

This is because with MEDIUMTEXT we can use a multibyte character set like utf-8.

256*3 = 768 = FAIL!
255*3 = 765 = OK :)

But UTF8 can have some char than need 4 bytes (as I understand). So I propose we limit the index to 191 to be on the safe side.

What about creating a new colon with a SHA from the data+cid+nid and using a query than has only one WHERE condition on this new field? This shouldn't be faster that the proposed solution from #18 that use combined index?

Your index choice seems reasonable and well-researched. I'm worried about time-outs though. Imagine:

PHP runs and starts the ALTER TABLE to add the index. It then times out, leaving the update undone. SQL may time-out, or it may complete. It will then re-run the update and I'm not sure what happens on various platforms if you add an index that already exists.

If executed from drush, it could block the site of an unacceptably long time. This is interesting:
http://stackoverflow.com/questions/4244685/create-an-index-on-a-huge-mys...

Accordingly, it might make sense to count the rows (which itself is slow with some engines) and only add the index if there are fewer than some threshold (maybe 100,000? 1,000,000?). Otherwise give a message saying that some tool should be used to add the index.

Any other ideas?

Ported to current 7.x-4.x and committed to 7.x-4.x and 8.x.

Great this breaks SQL Server and Oracle compatiblity, where indexexs cannot be that big.

The "elegant" solution is something like:

- Create a computed column based on the "data" column, maybe a HASH or similar.
- Instead of querying "data" column, query the computed column.

With this solution even index sizes will be kept small, depending on the hashing function used.

I was not able to find a way to create computed columns in the database api of Drupal, that would be a great addition to the database abstraction layer....

@david_garcia -- Can you quote what the limits are? A quick search seems to indicate 900 bytes for SQL Server and some OS and block/length limit on Oracle, with examples being in the > 700 byte area.

I don't see us hashing or going beyond the abstraction provided in core. Do you have any suggestions?

@DanChadwick -- Because of the lack of computed columns in database abstraction layer in core I cannot think of a better solution than the one proposed in this thread. Hashing in code and having to maintain a separate column is too much trouble, a computed column would do the trick.

I was actually trying to highlight the need of the abstraction layer to consider the possiblity of having such type of columns.

@david_garcia -- Are you sure the limits are a problem in SQL Server and Oracle? They don't seem to be from my quick read. References?

Re the abstraction layer / computed fields: core maintainers aren't going to read this issue. You might open an issue in the core queue and link it to this one.

@david_garcia and @DanChadwick: The latest patch broke compatibility for me as well, as I use SQL Server. I noted this on issue report SQL Server - PDOException: SQLSTATE[42000] when attempting update. In addition to Oracle, I'm sure this issue will happen to Postgres and other non MySQL users as well.

The temporary workaround of "commenting out the line with the index update. (line 1901 of webform.install)" let the rest of the update go through for me, however is there a way that I can do this manually?

Re #37 -- If you can determine a query that works with MSSql, please update this issue and set to active. There isn't a problem with Postgres, but may be with Oracle. I'm open to an engine-specific workaround.

If you can manually create an equivalent index, let us know the PHP code to do so. Absent that, comment out the index and proceed with the update. You just won't have indexing for unique validation.

@DanChadwick: I'm new to creating patches myself but I'll see what I can do. I have been looking into a possible solution since your reply from the other related issue report (thanks by the way). What kind of issues will not having indexing for unique validation cause for me when using webforms?

@jayhawkfan75 - I you figure out the SQL for MS, I can write the patch.

The consequence will be long delays if you have a webform that a) uses unique validation and b) has lots of data in other submissions for that webform and component. Not normally a problem until you have 10,000+ submissions.

Very good. Thanks again. I'll post back if I can figure that out.

@torotil: Sorry for the very late response but thanks!