New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

List topic sorted by subject/started_by is to slow #4820

Closed
albertlast opened this Issue Jul 6, 2018 · 2 comments

Comments

Projects
None yet
3 participants
@albertlast
Collaborator

albertlast commented Jul 6, 2018

In my test env its doubled or more the time run this query:
default qry:

         SELECT
            t.id_topic, t.num_replies, t.locked, t.num_views, t.is_sticky, t.id_poll, t.id_previous_board,
            COALESCE(lt.id_msg, COALESCE(lmr.id_msg, -1)) + 1 AS new_from,
             COALESCE(( SELECT 1 FROM smf_messages AS parti WHERE t.id_topic = parti.id_topic and parti.id_member = 1 LIMIT 1) , 0) as is_posted_in,
            
            t.id_last_msg, t.approved, t.unapproved_posts, ml.poster_time AS last_poster_time, t.id_redirect_topic,
            ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
            ml.poster_name AS last_member_name, ml.id_member AS last_id_member,
            COALESCE(meml.real_name, ml.poster_name) AS last_display_name, t.id_first_msg,
            mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
            mf.poster_name AS first_member_name, mf.id_member AS first_id_member,
            COALESCE(memf.real_name, mf.poster_name) AS first_display_name, ml.smileys_enabled AS last_smileys, mf.smileys_enabled AS first_smileys
            
         FROM smf_topics AS t
            INNER JOIN smf_messages AS ml ON (ml.id_msg = t.id_last_msg)
            INNER JOIN smf_messages AS mf ON (mf.id_msg = t.id_first_msg)
            LEFT JOIN smf_members AS meml ON (meml.id_member = ml.id_member)
            LEFT JOIN smf_members AS memf ON (memf.id_member = mf.id_member)
            LEFT JOIN smf_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = 1)
            LEFT JOIN smf_log_mark_read AS lmr ON (lmr.id_board = 36 AND lmr.id_member = 1)
            
         WHERE t.id_board = 36
            
         ORDER BY is_sticky DESC, id_last_msg DESC
      LIMIT 20 OFFSET 0
   in ...\Sources\MessageIndex.php line 379, which took 0.0074811 seconds at 0.03773808 into request.

Qry for order by Subject:

         SELECT
            t.id_topic, t.num_replies, t.locked, t.num_views, t.is_sticky, t.id_poll, t.id_previous_board,
            COALESCE(lt.id_msg, COALESCE(lmr.id_msg, -1)) + 1 AS new_from,
             COALESCE(( SELECT 1 FROM smf_messages AS parti WHERE t.id_topic = parti.id_topic and parti.id_member = 1 LIMIT 1) , 0) as is_posted_in,
            
            t.id_last_msg, t.approved, t.unapproved_posts, ml.poster_time AS last_poster_time, t.id_redirect_topic,
            ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
            ml.poster_name AS last_member_name, ml.id_member AS last_id_member,
            COALESCE(meml.real_name, ml.poster_name) AS last_display_name, t.id_first_msg,
            mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
            mf.poster_name AS first_member_name, mf.id_member AS first_id_member,
            COALESCE(memf.real_name, mf.poster_name) AS first_display_name, ml.smileys_enabled AS last_smileys, mf.smileys_enabled AS first_smileys
            
         FROM smf_topics AS t
            INNER JOIN smf_messages AS ml ON (ml.id_msg = t.id_last_msg)
            INNER JOIN smf_messages AS mf ON (mf.id_msg = t.id_first_msg)
            LEFT JOIN smf_members AS meml ON (meml.id_member = ml.id_member)
            LEFT JOIN smf_members AS memf ON (memf.id_member = mf.id_member)
            LEFT JOIN smf_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = 1)
            LEFT JOIN smf_log_mark_read AS lmr ON (lmr.id_board = 36 AND lmr.id_member = 1)
            
         WHERE t.id_board = 36
            
         ORDER BY is_sticky DESC, mf.subject
      LIMIT 20 OFFSET 0
   in ...\Sources\MessageIndex.php line 379, which took 0.01889086 seconds at 0.03537297 into request.

Core issue is that the sort columns in different tables and the database maybe do the sort/limit stuff to late.

Both can be fixed and both would improve the runtime
Options:

  • add all columns which are able to sort additional to the topic table -> would lead that the sorting time is in every mode the same
  • Use cte to force the database in early stage to redurce the amount of data:
WITH a AS (
  SELECT
    "t"."id_topic",
    "t"."id_first_msg",
    "mf"."id_member",
    "t"."id_last_msg"
  FROM
    "smf_topics" t
    JOIN "smf_messages" mf ON ("mf"."id_msg" = "t"."id_first_msg")
  WHERE
    "t"."id_board" = 36
  ORDER BY
    "t"."is_sticky" DESC,
    "mf"."subject" 
  LIMIT
    20
  OFFSET
    0
)
SELECT
  "t"."id_topic",
  "t"."num_replies",
  "t"."locked",
  "t"."num_views",
  "t"."is_sticky",
  "t"."id_poll",
  "t"."id_previous_board",
  COALESCE("lt"."id_msg", COALESCE("lmr"."id_msg", -1)) + 1 AS new_from,
  COALESCE((SELECT 1 FROM "smf_messages" parti WHERE "t"."id_topic" = "parti"."id_topic" AND "parti"."id_member" = 1 LIMIT 1), 0) AS is_posted_in,
  "t"."id_last_msg",
  "t"."approved",
  "t"."unapproved_posts",
  "ml"."poster_time" AS last_poster_time,
  "t"."id_redirect_topic",
  "ml"."id_msg_modified",
  "ml"."subject" AS last_subject,
  "ml"."icon" AS last_icon,
  "ml"."poster_name" AS last_member_name,
  "ml"."id_member" AS last_id_member,
  COALESCE("meml"."real_name", "ml"."poster_name") AS last_display_name,
  "t"."id_first_msg",
  "mf"."poster_time" AS first_poster_time,
  "mf"."subject" AS first_subject,
  "mf"."icon" AS first_icon,
  "mf"."poster_name" AS first_member_name,
  "mf"."id_member" AS first_id_member,
  COALESCE("memf"."real_name", "mf"."poster_name") AS first_display_name,
  "ml"."smileys_enabled" AS last_smileys,
  "mf"."smileys_enabled" AS first_smileys
FROM
  "a"
  JOIN "smf_topics" t ON ("a"."id_topic" = "t"."id_topic")
  JOIN "smf_messages" ml ON ("ml"."id_msg" = "a"."id_last_msg")
  JOIN "smf_messages" mf ON ("mf"."id_msg" = "a"."id_first_msg")
  LEFT JOIN "smf_members" meml ON ("meml"."id_member" = "ml"."id_member")
  LEFT JOIN "smf_members" memf ON ("memf"."id_member" = "mf"."id_member")
  LEFT JOIN "smf_log_topics" lt ON "lt"."id_topic" = "t"."id_topic" AND
                                   "lt"."id_member" = 1
  LEFT JOIN "smf_log_mark_read" lmr ON "lmr"."id_board" = 36 AND
                                       "lmr"."id_member" = 1
ORDER BY
  "is_sticky" DESC,
  "mf"."subject"

https://paste.depesz.com/s/fg
In my test env the runtime take the half amount of time even for the default query,
as you see this technic use cte -> works only with database how support this.

@jdarwood007

This comment has been minimized.

Member

jdarwood007 commented Jul 6, 2018

From what I can find, MySQL itself added CTE support in 8.0. I don't know about the major forks, but I guess they may have this. But we can't be sure. Is there a more mainstream compatible method that meets the more common deployments of MySQL out there?

@albertlast

This comment has been minimized.

Collaborator

albertlast commented Jul 7, 2018

After some sleep I realize that the cte version works easy without cte

SELECT
  "t"."id_topic",
  "t"."num_replies",
  "t"."locked",
  "t"."num_views",
  "t"."is_sticky",
  "t"."id_poll",
  "t"."id_previous_board",
  COALESCE("lt"."id_msg", COALESCE("lmr"."id_msg", -1)) + 1 AS new_from,
  COALESCE((SELECT 1 FROM "smf_messages" parti WHERE "t"."id_topic" = "parti"."id_topic" AND "parti"."id_member" = 1 LIMIT 1), 0) AS is_posted_in,
  "t"."id_last_msg",
  "t"."approved",
  "t"."unapproved_posts",
  "ml"."poster_time" AS last_poster_time,
  "t"."id_redirect_topic",
  "ml"."id_msg_modified",
  "ml"."subject" AS last_subject,
  "ml"."icon" AS last_icon,
  "ml"."poster_name" AS last_member_name,
  "ml"."id_member" AS last_id_member,
  COALESCE("meml"."real_name", "ml"."poster_name") AS last_display_name,
  "t"."id_first_msg",
  "mf"."poster_time" AS first_poster_time,
  "mf"."subject" AS first_subject,
  "mf"."icon" AS first_icon,
  "mf"."poster_name" AS first_member_name,
  "mf"."id_member" AS first_id_member,
  COALESCE("memf"."real_name", "mf"."poster_name") AS first_display_name,
  "ml"."smileys_enabled" AS last_smileys,
  "mf"."smileys_enabled" AS first_smileys
FROM
  (  SELECT
    "t"."id_topic",
    "t"."id_first_msg",
    "mf"."id_member",
    "t"."id_last_msg"
  FROM
    "smf_topics" t
    JOIN "smf_messages" mf ON ("mf"."id_msg" = "t"."id_first_msg")
  WHERE
    "t"."id_board" = 36
  ORDER BY
    "t"."is_sticky" DESC,
    mf.subject
  LIMIT
    20
  OFFSET
    0) "a"
  JOIN "smf_topics" t ON ("a"."id_topic" = "t"."id_topic")
  JOIN "smf_messages" ml ON ("ml"."id_msg" = "a"."id_last_msg")
  JOIN "smf_messages" mf ON ("mf"."id_msg" = "a"."id_first_msg")
  LEFT JOIN "smf_members" meml ON ("meml"."id_member" = "ml"."id_member")
  LEFT JOIN "smf_members" memf ON ("memf"."id_member" = "mf"."id_member")
  LEFT JOIN "smf_log_topics" lt ON "lt"."id_topic" = "t"."id_topic" AND
                                   "lt"."id_member" = 1
  LEFT JOIN "smf_log_mark_read" lmr ON "lmr"."id_board" = 36 AND
                                       "lmr"."id_member" = 1
ORDER BY
  "is_sticky" DESC,
  "mf"."subject"

https://paste.depesz.com/s/1T

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment