Create database index on the redirect column

I don't know off-hand what would need to query on the redirect field, but I don't think it happens that often (the condition for looking up based on the source column is in fact indexed currently)? That almost sounds like an entity field query.

I'm also seeing this non-stop on my new site's production databases processlist:

SELECT base.rid AS rid, base.hash AS hash, base.type AS
TYPE , base.uid AS uid, base.source AS source, base.source_options AS source_options, base.redirect AS redirect, base.redirect_options AS redirect_options, base.language AS language, base.status_code AS status_code, base.count AS count, base.access AS access
FROM redirect base
WHERE (
base.redirect = 'node/978110'
)

with the nid constantly changing.

I tracked down the source being the google_analytics_counter module.

    // Find possible redirects for this path using redirect_load_multiple() from module Redirect http://drupal.org/project/redirect
    if (function_exists('redirect_load_multiple')){
      //$sources = array();
      $redirectobjects = redirect_load_multiple(FALSE, array('redirect' => $path));
...

I'll go add an index.

@Dave Reid, did the info by JvE help? The index would really be helpful.

Attaching a patch that adds the index for new as well as existing installs; please review and commit. Thanks!

Patch in #4 works.

Tested on a clean Drupal install. Both on installation, and update. Against 7.x-1.0-rc1+8-dev.

Patch 4 looks good to me

Updated patch attached.

SimpleTest OK this time; setting the status back to RTBC.

The latest patch is missing the index definition in redirect_schema() so this index will not be created for new sites.

Oh, indeed, it fell through the cracks when updating the patch.
Line added back to redirect_schema() now — see the new patch.

+1 on the "Reviewed & tested by the community" - I just applied the patch and ran the update-hook on a quite large site that has 800.000+ redirects. It sped up redirect_field_attach_form() by about 500ms and applying the update-hook took about 10 seconds.

RTBC

Looks good.

@pifagor You accidentally committed the patch in #10 instead of #13. The former is missing the index definition in the schema.

@ciss, thanks

This change does not appear to work with Pantheon and Drupal 7 without converting to UTF8MB4.

I just upgraded to the latest 1.x-dev version (26-Jan-2020) on a non-utf8mb4 site and did not run into any errors.

@jackfoust Were you using 1.x or 2.x when you tried updating on your Pantheon site? What errors did you get?

I don't run Pantheon, so I can't test this myself. But given that it's a widely used platform, I think it's worth finding someone to run a few tests on a Pantheon installation and see what they come up with.

Some info here - https://www.drupal.org/project/redirect/issues/2815099