Uploaded image for project: 'phpBB'
  1. phpBB
  2. PHPBB-16431

Add index to improve acl_get_list database performance

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 3.2.7
    • None
    • Apache/2.4.41 (Amazon) PHP/7.2.24 , 5.7.mysql_aurora.2.04.5

      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);}}
      {{ ;}}

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

              Created:
              Updated: