It is common to load profiles by uid, type, status and is_default to get the default profile for a user. This is a bottleneck on busier commerce installs, especially with many anonymous profiles. I'd imagine the same problems could happen in non-commerce usages.

index to add

create index profile_test ON profile(uid, type, status, is_default);

The order of this index means it also allows indexes of

uid
uid, type
uid, type, status

Patch to follow

CommentFileSizeAuthor
#2 profile-add_index-3017788-2.patch2.05 KBsmccabe
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

smccabe created an issue. See original summary.

smccabe’s picture

smccabe CreditAttribution: smccabe as a volunteer and at Acro Commerce commented
FileSize
profile-add_index-3017788-2.patch2.05 KB
Anybody’s picture

Anybody
Primary language German
Location Porta Westfalica
CreditAttribution: Anybody at DROWL.de commented

This definitely makes sense. Can you perhaps give an overview which indexes are currently set on profiles? If none, this is definitely RTBC!
If not we should consider to update them.

It is common to load profiles by uid, type, status and is_default to get the default profile for a user.

Are these SQL statements generated in a public method of the module or called outside in other modules?

Thank you very much!

smccabe’s picture

smccabe CreditAttribution: smccabe as a volunteer and at Acro Commerce commented

The default indexes are:

profile_id
uuid
revision_id
type
uid

The statements are called outside the module primarily, as there are not really any searches or bulk loads within the module itself. Since these are common parameters of the profile, they are likely search parameters, although they are used in Commerce, which is the biggest use case for Profile.

Anybody’s picture

Anybody
Primary language German
Location Porta Westfalica
CreditAttribution: Anybody at DROWL.de commented

Hi smccabe,

thank you for your clarification. I agree it would make sense to add status to the indexes. We should now decide if one of the existing indexed should be altered or simply add another. For a good decision we could have a look at similar cases or how the current indexes are used. Perhaps replace uid by the new index would make sense for that decision tree?

mglaman’s picture

mglaman
Primary language English
Location WI, USA
CreditAttribution: mglaman at Centarro commented

The existing indexes are generated by field column definitions. Anything aggregated like this needs to be determined in the entity schema.

It'd be great if we had some EXPLAIN on queries (like even in Devel's Webprofiler.)

FWIW our ProfileStorage shows the benefit of this directly:

    return $this->loadByProperties([
      'uid' => $account->id(),
      'type' => $profile_type,
      'status' => $active,
    ]);

and

    $result = $this->loadByProperties([
      'uid' => $account->id(),
      'type' => $profile_type,
      'status' => TRUE,
      'is_default' => TRUE,
    ]);
mglaman’s picture

mglaman
Primary language English
Location WI, USA
CreditAttribution: mglaman at Centarro commented

@smccabe has offered to bring some old testing instances online and provide some results!

  • mglaman committed 1dae188 on 8.x-1.x authored by smccabe 
    Issue #3017788 by smccabe: Loading profiles can be slow because of...
mglaman’s picture

mglaman
Primary language English
Location WI, USA
CreditAttribution: mglaman at Centarro commented
Status: Needs review » Fixed

Rerolled and committed.

Anybody’s picture

Anybody
Primary language German
Location Porta Westfalica
CreditAttribution: Anybody at DROWL.de commented

Thank you @mglaman!! :)

Status: Fixed » Closed (fixed)

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