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

Add two indexes to posts table

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Unresolved
    • Icon: Minor Minor
    • None
    • 3.3.0
    • None

      Deleting a user on a medium size forum with zero messages takes multiples of seconds. Backend is mysql 5.7.29 / MyISAM. Looking into slow queries log, the following queries take seconds:

      UPDATE bbs_posts
      SET post_edit_user = 1
      WHERE post_edit_user = 103246;

      UPDATE bbs_posts
      SET post_delete_user = 1
      WHERE post_delete_user = 103246;

       

      Adding two indexes makes user delete operation sub-second as expected. Suggesting adding the following indexes to the posts table:

       

      create index post_edit_user on ...posts (post_edit_user);

      create index post_delete_user on ...posts (post_delete_user);

       

      From trade-off perspective, since we optimize "for read" small additional tax on insert should be justifiable.

       

            Unassigned Unassigned
            UNRULY UNRULY [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated: