This query could be sped up by an index. I don't know what function is calling it though. Adding an index on the redirect column speeds it up.

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/886244'
)
CommentFileSizeAuthor
#13 add_index_on_redirect_column-2194099-13.patch710 bytesVacilando
#10 add_index_on_redirect_column-2194099-10.patch555 bytesVacilando
#4 add_index_on_redirect_column-2194099-4.patch785 bytesVacilando
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented
Status: Active » Postponed (maintainer needs more info)

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.

JvE’s picture

JvE CreditAttribution: JvE commented

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.

Vacilando’s picture

Vacilando CreditAttribution: Vacilando commented
Status: Postponed (maintainer needs more info) » Active

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

Vacilando’s picture

Vacilando CreditAttribution: Vacilando commented
Status: Active » Needs review
Related issues: +#2290813: High db load with redirect module
FileSize
add_index_on_redirect_column-2194099-4.patch785 bytes

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

rp7’s picture

rp7 CreditAttribution: rp7 commented

Patch in #4 works.

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

nicrodgers’s picture

nicrodgers
Primary language English
Location Monmouthshire, UK
CreditAttribution: nicrodgers commented

Patch 4 looks good to me

nicrodgers’s picture

nicrodgers
Primary language English
Location Monmouthshire, UK
CreditAttribution: nicrodgers commented
Status: Needs review » Reviewed & tested by the community

Dave Reid queued 4: add_index_on_redirect_column-2194099-4.patch for re-testing.

Status: Reviewed & tested by the community » Needs work

The last submitted patch, 4: add_index_on_redirect_column-2194099-4.patch, failed testing.

Vacilando’s picture

Vacilando CreditAttribution: Vacilando as a volunteer commented
Status: Needs work » Needs review
FileSize
add_index_on_redirect_column-2194099-10.patch555 bytes

Updated patch attached.

Vacilando’s picture

Vacilando CreditAttribution: Vacilando as a volunteer commented
Status: Needs review » Reviewed & tested by the community

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

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented
Status: Reviewed & tested by the community » Needs work

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

Vacilando’s picture

Vacilando CreditAttribution: Vacilando as a volunteer commented
Status: Needs work » Reviewed & tested by the community
FileSize
add_index_on_redirect_column-2194099-13.patch710 bytes

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

danquah’s picture

danquah CreditAttribution: danquah commented

+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.

pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at GOLEMS GABB for GOLEMS GABB commented

RTBC

alex_optim’s picture

alex_optim
Location 🇺🇦 Rivne
CreditAttribution: alex_optim at GOLEMS GABB for GOLEMS GABB commented

Looks good.

pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented

  • pifagor committed ba29054 on 7.x-1.x authored by Vacilando 
    Issue #2194099 by Vacilando, Dave Reid, nicrodgers, pifagor, JvE, rp7,...

  • pifagor committed 4b84b7c on 7.x-2.x 
    Issue #2194099 by Vacilando, Dave Reid, nicrodgers, pifagor, JvE, rp7,...
pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented
Status: Reviewed & tested by the community » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

ciss’s picture

ciss CreditAttribution: ciss at yousign GmbH commented

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

pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented
Status: Closed (fixed) » Reviewed & tested by the community

  • pifagor committed 4fdcb8f on 7.x-1.x 
    Issue #2194099 by Vacilando, pifagor, Dave Reid, nicrodgers, JvE, rp7,...

  • pifagor committed 6d8aa2a on 7.x-2.x 
    Issue #2194099 by Vacilando, pifagor, Dave Reid, nicrodgers, JvE, rp7,...
pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented

@ciss, thanks

pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented
Status: Reviewed & tested by the community » Fixed
jackfoust’s picture

jackfoust CreditAttribution: jackfoust as a volunteer and at Muni-Link commented

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

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

hargobind’s picture

hargobind
he/him
Location Austin, Texas
CreditAttribution: hargobind commented

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.

pifagor’s picture

pifagor
Location 🇺🇦 Rivne
CreditAttribution: pifagor at Drupal Ukraine Community, GOLEMS GABB for GOLEMS GABB, Drupal Ukraine Community commented

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