Uploaded image for project: 'phpBB3'
  1. phpBB3
  2. PHPBB3-16431

Add index to improve acl_get_list database performance

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.2.7
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      Apache/2.4.41 (Amazon) PHP/7.2.24 , 5.7.mysql_aurora.2.04.5

      Description

      I found the acl_get_list query to take ~7 seconds on a beefy AWS RDS Aurora box.
      This was being called often as we use stop forum spam. A github issue showed some others had similar issues: https://github.com/rmcgirr83/stopforumspam/issues/19

      acl_get_list:

      https://area51.phpbb.com/docs/code/3.1.x/phpbb/auth/auth.html#method_acl_get_list

      Tried about 20 index variations, but found that mysql only liked putting an index on all 4 columns in the user_group table. I am aware this is odd, but after not getting meaningful performance improvements without it, this is my recommendation for an enhancement to the DDL in phpbb.

       

      ALTER TABLE {{phpbb_user_group}}
      {{ ADD INDEX {{ acl_get_list_tuning}} (group_id ASC, user_pending ASC, user_id ASC, group_leader ASC);}}
      {{ ;}}

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            mj44 mj44
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated: