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

Slow query while retrieving privmsg

    XMLWordPrintable

Details

    Description

      I was examining slow queries in my database. The slowest was

      SELECT t.*, p.root_level, p.message_time, p.message_subject, p.icon_id, p.to_address, p.message_attachment, p.bcc_address, u.username, u.username_clean, u
      .user_colour
      FROM phpbb_privmsgs_to t, phpbb_privmsgs p, phpbb_users u
      WHERE t.user_id = N
      AND p.author_id = u.user_id
      AND t.folder_id = N
      AND t.msg_id = p.msg_id
      ORDER BY p.message_time DESC
      LIMIT N

      My Info about phpbb tables
      phpbb_privmsgs_to 5919959 rows
      phpbb_privmsgs 4055176 rows
      phpbb_users 72249 rows

      Sorting by p.message_time forces mysql to create a temporary table

      ID SELECT TYPE TABLE TYPE POSSIBLE KEYS KEY KEY LEN REF ROWS EXTRA
      1 SIMPLE t ref msg_id,usr_flder_id usr_flder_id 7 const,const 359 Using temporary; Using filesort
      1 SIMPLE p eq_ref PRIMARY,author_id PRIMARY 3 new_bb.t.msg_id 1
      1 SIMPLE u eq_ref PRIMARY PRIMARY 3 new_bb.p.author_id 1

      Elapsed: 1.96515s

      I believe that the order of messages can be sorted not by message_time as it takes long time but by msg_id. It takes less time to complete the query for me and uses much less resouses.

      Here is a simple query

      explain SELECT t.*, p.root_level, p.message_time, p.message_subject, p.icon_id, p.to_address, p.message_attachment, p.bcc_address, u.username, u.username_clean, u.user_colour
      FROM phpbb_privmsgs_to t, phpbb_privmsgs p, phpbb_users u
      WHERE t.user_id = 5351
      AND p.author_id = u.user_id
      AND t.folder_id = 0
      AND t.msg_id = p.msg_id
      ORDER BY t.msg_id DESC
      LIMIT 50

      1 SIMPLE t ref msg_id,usr_flder_id usr_flder_id 7 const,const 359 Using where; Using filesort
      1 SIMPLE p eq_ref PRIMARY,author_id PRIMARY 3 new_bb.t.msg_id 1
      1 SIMPLE u eq_ref PRIMARY PRIMARY 3 new_bb.p.author_id 1
      Elapsed: 0.00241s

      What can community tell about this fact? Can ordering by msg_id differ from ordering by message time? I think that messages will be in the same order. But sorting this way is much faster.

      Attachments

        Activity

          People

            CHItA CHItA
            ilantipov ilantipov [X] (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: