4

I'm working on improving performance for a Drupal 7 website and noticed that all of the non-value columns (entity_type, bundle, delete, etc) are indexed, but the actual value field isn't...does this make sense to anyone?

I know Drupal uses the other fields to join against other tables, but any views/custom queries that are searching for nodes with specific field values are going to suffer are they not?

Is there some reason why the value column isn't indexed by default? Will it cause problems if an index is added?


The specific field I'm looking to index is a date field if that provides any more insight into the situation

5

I'm not an expert in this but I think it's probably due to the fact MySQL enforces a maximum key length for indexes. For InnoDB this is 3500 bytes (further limited to 3072 bytes by MySQL itself), and for MYISAM it's 1000 bytes.

Whether or not the value columns are indexed is actually done on a per field-type basis. For example, the long text field type (for fields like the node body) has no index on the fieldname_value column, but it does have an index on the fieldname_format column.

The fieldname_format column is a varchar limited to 255 characters, and so would not produce an index key that is too long. Conversely the fieldname_value column is a longtext type, which has the potential to create a key that would be too long. (You can specify a prefix length to get around this but I don't think Drupal supports it).

As another example, if you then look at a table for an image (or file) field type you'll notice there is an index on the fieldname_fid column. Because this field is an integer type it naturally lends itself to be indexed, without having to worry about the maximum index key length.

I think it basically comes down to the type of data the field is storing. entity_type/bundle/etc. are all integer columns and so can be easily indexed by whatever storage engine you're using, while text types (or varchar types with a large enough length) will create an index key that's too large for the storage engine to handle.

Hope that helps a bit

0

Just to complete Clive's answer above, nothing can prevent you from adding indexes wherever you want, and it sure could improve performance, but be aware that it can be counter-productive.

Indexes can be used automatically by MySQL, even in cases you wouldn't expect, so you have to be careful and use some EXPLAIN queries to ensure your indexes get used at the moment you want and at no other time.

Long story short : it is a case by case problem, which explians why the devs didn't try to guess what performance gains one would be looking after.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.