12

I have been searching and reading about this, but I have not seen anything definitive about the topic of adding indexes to Drupal tables (both core and contrib).

My main concern is what happens with any custom indexes when you update core or contrib code and there are schema changes. What happens in this case?

EDIT:

I think some context may help. I am primarily concerned with adding indexes to tables to tune site performance (queries showing up in the slow query log, pages with slow views, etc). This may involve adding an index to a table in someone else's module. For example

  1. I install module foo
  2. Module foo creates table foo
  3. I add an index to table foo
  4. Module foo has an update that changes the schema

What happens?

7

Yes, it could result in problems.

If a module wants to do something with the column on which you have added an index, it will remove it's own indexes and then do whatever operation it intends to do.

What exactly will happen will depend on your database type and the actually executed operation. For example, a rename of the column will work fine with MySQL, but will fail on PostgreSQL. But if it tries to delete that column (maybe after migrating data to a different table/colun), it will fail.

The chances that this will happen are relatively low, at least for minor updates (does depend on the actual module however. I usually don't add any changes which could break something to minor releases), but it is possible.

My suggestion would be that you try to work together with the module maintainers. If the problematic query/ies are from the module itself, then the maintainers will probably happily add the indexes, if you provide a patch. Provide DESCRIBE output of the problematic query/ies before and after adding the index. Also provide a patch that updates the schema (include an update function to set it for existing installations).

Someone who is actively working on performance related things and does the above really well is catch, here is an example: http://drupal.org/node/983950

2

As reported in DatabaseSchema_pgsql::changeField, and in db_change_field():

IMPORTANT NOTE: To maintain database portability, you have to explicitly recreate all indices and primary keys that are using the changed field.

That means that you have to drop all affected keys and indexes with db_drop_{primary_key,unique_key,index}() before calling db_change_field(). To recreate the keys and indices, pass the key definitions as the optional $new_keys argument directly to db_change_field().

For example, suppose you have:

$schema['foo'] = array(
  'fields' => array(
    'bar' => array('type' => 'int', 'not null' => TRUE)
  ),
  'primary key' => array('bar')
);

and you want to change foo.bar to be type serial, leaving it as the primary key. The correct sequence is:

db_drop_primary_key($ret, 'foo');
db_change_field($ret, 'foo', 'bar', 'bar',
  array('type' => 'serial', 'not null' => TRUE),
  array('primary key' => array('bar'))
);

Similar code is reported for Drupal 7.

Keep in mind that, for my experience, you cannot remove a primary key that uses a serial field. On Drupal 6, I got an error all times I tried doing that; I didn't try that on Drupal 7.

Apart that, I don't know any other issue you could have with database indexes.

About adding an index to a database table that is created from another module, I would not suggest doing that, because:

  • A module doesn't drop an index for a field that is being changed, if the module itself didn't create that index. It would not be possible for the module to do that because it doesn't know the name of the index.
  • Modifying a database table created by another module is never a good idea, even in the case the module is a core module. If there is another module that changes the same table, how could the modules handle any conflict they have with each other, or with the changes the core module would apply to its own database?

If the database table is being created from another module (a core module or a third-party module), I would suggest to open a feature request for the module, providing a use case for using a new index; if there are any performance issue, adding an index could be the desired thing to do.

If you are going to add an index to a table created from another module to your own site, then be prepared to any changes you need to do to your custom module every time the module is updated, and before you install it in your own site.
It is you who can decide if the extra work is worth the performance you get. Personally, I don't think it is worth, though.

  • Thanks. How would this work if I add an index to a table to combat a slow query, not just a change to my own module? I'll try to edit my question to be a bit more clear when I get a chance. – mpdonadio Jun 7 '11 at 14:45
  • 3
    Also you may consider using DB Tuner which is handy to know which indexes to create. – tostinni Jun 7 '11 at 16:12
  • @tostinni Yeah, the question was almost direct related to implementing recommendations from DB Tuner. – mpdonadio Jun 7 '11 at 16:36

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.