Performance Issue during Indexing - search_dataset.sid_type unique key should be an Index

And here's the patch...

This sid_type should in fact be a primary key.

The change from index to unique was consciously made during the 6.x development cycle. It lead to some problems (such as collations problems solved in #218403: Duplicate entry errors in search indexer), but it's a good choice.

I'm not too clear on the differences here. But I do know that the current "unique key" implementation is significantly impacting this DELETE which is done on every single node that is indexed. If you know a better way, please offer the patch. Thanks!

I know a better way: just make this key a primary key.

@Damien: How is Primary key better than a Unique key in this instance?

A unique key is just a constraint, whereas a primary key is an index. You get no performance gain from constraints, but you do from indexes.

@douggreen: In fact, for MySQL the difference is not very clear. MySQL also creates an index for UNIQUE KEY, but it's not clear to me if there is prefix and suffix compression for these indexes, nor whereas MySQL can use it to lookup values. Moreover, PRIMARY KEYS are clustered (the physical order of rows change), whereas other index types are non-clustered.

For the {search_dataset} particular case, a PRIMARY KEY is what makes sense anyway.

I do know that the unique key in mysql is giving terrible performance on the DELETE FROM {search_dataset} WHERE sid = %d AND type = '%s', so it surprises me to hear that "MySQL also creates an index for UNIQUE KEY."

Subscribe.

Reroll patch #1 and add description to update function.

Committed to CVS HEAD. Thanks.

Sorry, why removing the unique constraint? It makes total sense!

Here is a revert of #10, changing the unique constraints in search_index and search_dataset by primary keys.

I think you're right. Committed the follow-up patch.

Sorry, finally forgot to drop the redundant 'word' index in the schema.

There's an update script in system.install running on the search tables, but search module and its tables may not even be installed! This causes errors at update.php. The update script should be in search.install instead.

Oups.

This no longer applies because of some whitespace changes in #303889: Impossible to update D6 -> D7 but should be a quick re-roll.

I tested this on top of #46 in http://drupal.org/node/303889, and that combination made update.php work again.

Third time's a charm? :)

I'm pretty sure this issue will came back to haunt us. A ghost will step in, and say "this is an *horrible* idea, please revert". The only question is: when will that happen?

Automatically closed -- issue fixed for two weeks with no activity.

Automatically closed -- issue fixed for two weeks with no activity.

Automatically closed -- issue fixed for two weeks with no activity.