Add more indexes to {project_release_nodes}

I originally used project_releases_version_minor_version_patch_version_extra_weight_version_extra as the name for one of the indexes, but it gave me a Identifier name is too long error, so I used project_releases_versions.

Do you have examples of the queries you want to optimize?

The database engine cannot use an index to optimize a ORDER BY if it hasn't used the same index to filter the rows, so you might need to add more columns to your second index.

Actually I did not take a look to the queries mentioned in #642094: Move the code to build the query that drives project_release_find_latest_releases() into a shared helper function, and it seems that this need to be re-thinked.

Here a example query generated by project_release_query_releases_by_branch():

SELECT n.nid, n.title, n.created, r.*
FROM {node} n
INNER JOIN {project_release_nodes} r ON r.nid = n.nid
WHERE
  (r.pid = %d)
  AND (r.version_api_tid = %d)
  AND (r.version_major = %d)
  AND (n.status = %d)
ORDER BY
  r.rebuild,
  r.version_minor DESC,
  r.version_patch DESC,
  r.version_extra_weight DESC,
  r.version_extra_delta DESC,
  r.version_extra DESC

Not sure about what index would help here.

No index can help for the order by in this case. It could help to have an additional condition on n.type = 'project_release'. After that, supposing we have an index on r.pid already I don't think we can help much more.

That said, we could make this query lightning fast by denormalizing the status column in the project_release_nodes table.

#1: 0001-Add-more-indexes-to-project_release_nodes.patch queued for re-testing.

This table no longer exists in D7.