Re: Help, why is this SQL statement not using an available index?

[Rob Binary]

The system is Drupal 6. The current version is Drupal 7 so this is an older application, and that always comes with some challenges.

The key table is node. nid is node ID, vid is Version ID.

In the case where the system is being used with multiple versions of a node, or if it is just being used to keep a change history of each node, there can be multiple vids for any give nid. The node table holds information that's common to all revisions, the node_revision table contains information about the node that can change between revisons of the node table.

When various fields are added to a node it is done by creating a table per field and then setting up a relationship between the node and the field. This needs to be done through vid for versioning. However, there are many times when it is more expedient to connect to the node table through the nid without regard for the revision. This is why you often see the nid and the vid in multiple tables -- a matter of being able to take the most expedient join depending on requirements.

The older version of Drupal, this one, was centered around MyISAM so where you see some tables in InnoDB they were either manually changed over or are tables created from custom code where the programmer made that decision.

Current version of Drupal using InnoDB entirely so part of the challenge is dealing with older code/practices.

I'll look into your suggestions. Give me a couple of days to try what I can change and I'll get back to you with the results.