דילוג לתוכן
  • דף הבית
  • קטגוריות
  • פוסטים אחרונים
  • משתמשים
  • חיפוש
  • חוקי הפורום
כיווץ
תחומים

תחומים - פורום חרדי מקצועי

💡 רוצה לזכור קריאת שמע בזמן? לחץ כאן!
  1. דף הבית
  2. תכנות
  3. ייעול שאילתת MYSQL

ייעול שאילתת MYSQL

מתוזמן נעוץ נעול הועבר תכנות
15 פוסטים 3 כותבים 448 צפיות 3 עוקבים
  • מהישן לחדש
  • מהחדש לישן
  • הכי הרבה הצבעות
תגובה
  • תגובה כנושא
התחברו כדי לפרסם תגובה
נושא זה נמחק. רק משתמשים עם הרשאות מתאימות יוכלו לצפות בו.
  • WWWW מנותק
    WWWW מנותק
    WWW
    כתב ב נערך לאחרונה על ידי
    #1

    יש לי שאילתא שלוקחת כ 30 שניות.

    • הטבלה המדוברת מכילה כמה עשרות מיליון שורות.
      להלן השאילתא וה EXPLAIN ANALYZE שלו:
    EXPLAIN ANALYZE 
    SELECT
    count as כניסות,
      sum as סיכום ,
      A.t_user as משתמש,
        B.group_id as קבוצה
    FROM
      (
        SELECT
          sum(elapsed) / 60 as sum,
          t_user,
          COUNT(id) as count
        FROM
          (
            SELECT
              *
            from
              cdr
            WHERE
              end_ts > UNIX_TIMESTAMP('2023-7-1 00:00:00')
              AND end_ts < UNIX_TIMESTAMP('2023-7-2 00:00:00')
              AND elapsed < 7200
          ) as c
        group by
          t_user  ) as A
      INNER JOIN (
        SELECT
          *
        from
          re_grp
      ) as B ON B.reg_exp = A.t_user
      WHERE  sum > 100
      AND B.group_id = "14"
    ;
    
    -> Limit: 200001 row(s)  (cost=251.27 rows=688) (actual time=24439.014..24439.014 rows=0 loops=1)
        -> Nested loop inner join  (cost=251.27 rows=688) (actual time=24439.012..24439.012 rows=0 loops=1)
            -> Index lookup on re_grp using group_idx (group_id=14)  (cost=10.34 rows=10) (actual time=0.376..1.341 rows=10 loops=1)
            -> Filter: (re_grp.reg_exp = A.t_user)  (cost=0.26..17.90 rows=69) (actual time=2443.766..2443.766 rows=0 loops=10)
                -> Index lookup on A using <auto_key0> (t_user=re_grp.reg_exp)  (actual time=0.002..0.002 rows=0 loops=10)
                    -> Materialize  (cost=0.00..0.00 rows=0) (actual time=2443.766..2443.766 rows=0 loops=10)
                        -> Filter: ((sum(cdr.elapsed) / 60) > 100)  (actual time=24436.782..24437.519 rows=58 loops=1)
                            -> Table scan on <temporary>  (actual time=0.003..0.135 rows=1747 loops=1)
                                -> Aggregate using temporary table  (actual time=24436.010..24436.269 rows=1747 loops=1)
                                    -> Filter: ((cdr.end_ts > <cache>(unix_timestamp('2023-7-1 00:00:00'))) and (cdr.end_ts < <cache>(unix_timestamp('2023-7-2 00:00:00'))) and (cdr.elapsed < 7200))  (cost=3037453.91 rows=928195) (actual time=15394.662..24430.348 rows=11270 loops=1)
                                        -> Table scan on cdr  (cost=3037453.91 rows=25068783) (actual time=0.575..22594.434 rows=26078024 loops=1)
    

    אין שום אינדקסים, למעט לטבלה re_grp שהוספתי אינדקס שלא עזר הרבה.
    ניסיתי להוסיף ל cdr אינדקסים, אבל זה רק עשה יותר גרוע...
    אשמח להכוונה מה יכול להאיץ את השאילתא.
    תודה!

    WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

    yossizY תגובה 1 תגובה אחרונה
    0
    • WWWW WWW

      יש לי שאילתא שלוקחת כ 30 שניות.

      • הטבלה המדוברת מכילה כמה עשרות מיליון שורות.
        להלן השאילתא וה EXPLAIN ANALYZE שלו:
      EXPLAIN ANALYZE 
      SELECT
      count as כניסות,
        sum as סיכום ,
        A.t_user as משתמש,
          B.group_id as קבוצה
      FROM
        (
          SELECT
            sum(elapsed) / 60 as sum,
            t_user,
            COUNT(id) as count
          FROM
            (
              SELECT
                *
              from
                cdr
              WHERE
                end_ts > UNIX_TIMESTAMP('2023-7-1 00:00:00')
                AND end_ts < UNIX_TIMESTAMP('2023-7-2 00:00:00')
                AND elapsed < 7200
            ) as c
          group by
            t_user  ) as A
        INNER JOIN (
          SELECT
            *
          from
            re_grp
        ) as B ON B.reg_exp = A.t_user
        WHERE  sum > 100
        AND B.group_id = "14"
      ;
      
      -> Limit: 200001 row(s)  (cost=251.27 rows=688) (actual time=24439.014..24439.014 rows=0 loops=1)
          -> Nested loop inner join  (cost=251.27 rows=688) (actual time=24439.012..24439.012 rows=0 loops=1)
              -> Index lookup on re_grp using group_idx (group_id=14)  (cost=10.34 rows=10) (actual time=0.376..1.341 rows=10 loops=1)
              -> Filter: (re_grp.reg_exp = A.t_user)  (cost=0.26..17.90 rows=69) (actual time=2443.766..2443.766 rows=0 loops=10)
                  -> Index lookup on A using <auto_key0> (t_user=re_grp.reg_exp)  (actual time=0.002..0.002 rows=0 loops=10)
                      -> Materialize  (cost=0.00..0.00 rows=0) (actual time=2443.766..2443.766 rows=0 loops=10)
                          -> Filter: ((sum(cdr.elapsed) / 60) > 100)  (actual time=24436.782..24437.519 rows=58 loops=1)
                              -> Table scan on <temporary>  (actual time=0.003..0.135 rows=1747 loops=1)
                                  -> Aggregate using temporary table  (actual time=24436.010..24436.269 rows=1747 loops=1)
                                      -> Filter: ((cdr.end_ts > <cache>(unix_timestamp('2023-7-1 00:00:00'))) and (cdr.end_ts < <cache>(unix_timestamp('2023-7-2 00:00:00'))) and (cdr.elapsed < 7200))  (cost=3037453.91 rows=928195) (actual time=15394.662..24430.348 rows=11270 loops=1)
                                          -> Table scan on cdr  (cost=3037453.91 rows=25068783) (actual time=0.575..22594.434 rows=26078024 loops=1)
      

      אין שום אינדקסים, למעט לטבלה re_grp שהוספתי אינדקס שלא עזר הרבה.
      ניסיתי להוסיף ל cdr אינדקסים, אבל זה רק עשה יותר גרוע...
      אשמח להכוונה מה יכול להאיץ את השאילתא.
      תודה!

      yossizY מנותק
      yossizY מנותק
      yossiz
      כתב ב נערך לאחרונה על ידי
      #2

      @WWW לפום ריהטא בלי עיון גמור, כמעט כל הזמן הולך על סריקת טבלת cdr, לכאורה אינדקס על end_ts היה עוזר

      📧 יוסי@מייל.קום | 🌎 בלוג | ☕ קפה

      WWWW 2 תגובות תגובה אחרונה
      3
      • yossizY yossiz

        @WWW לפום ריהטא בלי עיון גמור, כמעט כל הזמן הולך על סריקת טבלת cdr, לכאורה אינדקס על end_ts היה עוזר

        WWWW מנותק
        WWWW מנותק
        WWW
        כתב ב נערך לאחרונה על ידי
        #3

        @yossiz 👍 👍 👍
        אלף תודות!
        ירד לפחות מחצי שנייה!!
        אני ניסתי להוסיף אינדקסים מרובי עמודות, שרק גרעו...

        WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

        WWWW תגובה 1 תגובה אחרונה
        3
        • WWWW WWW

          @yossiz 👍 👍 👍
          אלף תודות!
          ירד לפחות מחצי שנייה!!
          אני ניסתי להוסיף אינדקסים מרובי עמודות, שרק גרעו...

          WWWW מנותק
          WWWW מנותק
          WWW
          כתב ב נערך לאחרונה על ידי WWW
          #4

          @yossiz אבל זה לא עזר אם חוזר הרבה רשומות (בטווח של יום זה כן מייעל, כנ"ל), אם אני משנה את טווח התאריכים לשנה זה עדיין לוקח 30 שניות... כנראה שכן צריך לכלול את elapsed

          WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

          תגובה 1 תגובה אחרונה
          1
          • yossizY yossiz

            @WWW לפום ריהטא בלי עיון גמור, כמעט כל הזמן הולך על סריקת טבלת cdr, לכאורה אינדקס על end_ts היה עוזר

            WWWW מנותק
            WWWW מנותק
            WWW
            כתב ב נערך לאחרונה על ידי
            #5

            @yossiz עשיתי אינדקס שכולל גם את elapsed זה עדיין לא עוזר על טווח של יותר מחודש, הוא לא משתמש באינדקס אם זה טווח גדול, לא ברור למה.
            כאן אני רואה שיש כזה דבר שאם המנוע משער סטטיסטי שזה יהיה יותר מידי רשומות (מעל 30% מהטבלה, במקרה שלי זה לא נכון) הוא לא משתמש באינדקס כי זה לא חוסך כלום, בגלל שאחר כך הוא יצטרך לחפש את כל שאר העמודות בדיסק.

            WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

            תגובה 1 תגובה אחרונה
            0
            • dovidD מנותק
              dovidD מנותק
              dovid
              ניהול
              כתב ב נערך לאחרונה על ידי dovid
              #6

              @WWW עזוב שניה ביצועים, מה זה השאילתה המסובכת וההזויה הזאת?
              למה לא פשוט:

              SELECT
                cdr.t_user,
                grp.group_id,
                sum(cdr.elapsed) / 60 as sum,
                COUNT(cdr.id) as count
              FROM cdr 
              INNER JOIN re_grp as grp ON grp.reg_exp = cdr.t_user
              WHERE 
                 cdr.end_ts > UNIX_TIMESTAMP('2023-7-1 00:00:00') AND
                 cdr.end_ts < UNIX_TIMESTAMP('2023-7-2 00:00:00') AND
                 cdr.elapsed < 7200 AND
                 grp.group_id = "14"
              GROUP BY cdr.t_user
              HAVING  sum(cdr.elapsed) > 100
              

              זה לא מה שאתה צריך?

              • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
              • בכל נושא אפשר ליצור קשר dovid@tchumim.com
              WWWW תגובה 1 תגובה אחרונה
              3
              • dovidD dovid

                @WWW עזוב שניה ביצועים, מה זה השאילתה המסובכת וההזויה הזאת?
                למה לא פשוט:

                SELECT
                  cdr.t_user,
                  grp.group_id,
                  sum(cdr.elapsed) / 60 as sum,
                  COUNT(cdr.id) as count
                FROM cdr 
                INNER JOIN re_grp as grp ON grp.reg_exp = cdr.t_user
                WHERE 
                   cdr.end_ts > UNIX_TIMESTAMP('2023-7-1 00:00:00') AND
                   cdr.end_ts < UNIX_TIMESTAMP('2023-7-2 00:00:00') AND
                   cdr.elapsed < 7200 AND
                   grp.group_id = "14"
                GROUP BY cdr.t_user
                HAVING  sum(cdr.elapsed) > 100
                

                זה לא מה שאתה צריך?

                WWWW מנותק
                WWWW מנותק
                WWW
                כתב ב נערך לאחרונה על ידי
                #7

                @dovid צודק, לא אני כתבתי את השאילתא... בכל מקרה זה לוקח אותו זמן...

                WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

                dovidD תגובה 1 תגובה אחרונה
                0
                • WWWW WWW

                  @dovid צודק, לא אני כתבתי את השאילתא... בכל מקרה זה לוקח אותו זמן...

                  dovidD מנותק
                  dovidD מנותק
                  dovid
                  ניהול
                  כתב ב נערך לאחרונה על ידי dovid
                  #8

                  @WWW כתב בייעול שאילתת MYSQL:

                  בכל מקרה זה לוקח אותו זמן...

                  תוכל לצרף EXPLAIN עדכני לשאילתה שניסחתי?

                  • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
                  • בכל נושא אפשר ליצור קשר dovid@tchumim.com
                  WWWW תגובה 1 תגובה אחרונה
                  1
                  • dovidD dovid

                    @WWW כתב בייעול שאילתת MYSQL:

                    בכל מקרה זה לוקח אותו זמן...

                    תוכל לצרף EXPLAIN עדכני לשאילתה שניסחתי?

                    WWWW מנותק
                    WWWW מנותק
                    WWW
                    כתב ב נערך לאחרונה על ידי
                    #9

                    @dovid

                    -> Limit: 200001 row(s)  (actual time=31750.842..31750.842 rows=0 loops=1)
                        -> Filter: (sum(cdr.elapsed) > 100)  (actual time=31750.837..31750.837 rows=0 loops=1)
                            -> Table scan on <temporary>  (actual time=0.004..0.005 rows=1 loops=1)
                                -> Aggregate using temporary table  (actual time=31750.451..31750.453 rows=1 loops=1)
                                    -> Nested loop inner join  (cost=3766093.75 rows=46610) (actual time=22060.691..31750.388 rows=1 loops=1)
                                        -> Filter: ((cdr.end_ts > <cache>(unix_timestamp('2023-7-1 00:00:00'))) and (cdr.end_ts < <cache>(unix_timestamp('2023-7-2 00:00:00'))) and (cdr.elapsed < 7200) and (cdr.t_user is not null))  (cost=3049122.50 rows=932200) (actual time=22042.090..31708.494 rows=11270 loops=1)
                                            -> Table scan on cdr  (cost=3049122.50 rows=25176946) (actual time=1.345..29896.727 rows=26187820 loops=1)
                                        -> Filter: (grp.group_id = 14)  (cost=0.67 rows=0) (actual time=0.004..0.004 rows=0 loops=11270)
                                            -> Single-row index lookup on grp using unique_index (reg_exp=cdr.t_user), with index condition: (grp.reg_exp = cdr.t_user)  (cost=0.67 rows=1) (actual time=0.003..0.003 rows=1 loops=11270)
                    
                    

                    אבל זה בלי אינדקס כרגע על CDR.

                    WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

                    תגובה 1 תגובה אחרונה
                    0
                    • dovidD מנותק
                      dovidD מנותק
                      dovid
                      ניהול
                      כתב ב נערך לאחרונה על ידי dovid
                      #10

                      אני לא יודע מה יש למטב חוץ מאינדקס על end_ts,
                      גם בקשר לelapsed אני לא יודע אם זה יהיה יעיל לאנדקס.
                      לזכרוני אפשר להכריח את השאילתה לעבוד עם אינדקס.

                      • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
                      • בכל נושא אפשר ליצור קשר dovid@tchumim.com
                      WWWW תגובה 1 תגובה אחרונה
                      1
                      • dovidD dovid

                        אני לא יודע מה יש למטב חוץ מאינדקס על end_ts,
                        גם בקשר לelapsed אני לא יודע אם זה יהיה יעיל לאנדקס.
                        לזכרוני אפשר להכריח את השאילתה לעבוד עם אינדקס.

                        WWWW מנותק
                        WWWW מנותק
                        WWW
                        כתב ב נערך לאחרונה על ידי
                        #11

                        @dovid את האמת, שיש לי פה בעיה עם אינדקס, הטבלה מיועדת 99% לכתיבה...
                        כנראה מה שאני אעשה בסוף: טבלת מראה בDB אחר עם אינדקסים שיתעדכן פעם בשעה.
                        השאלה אם האינדקסים יעזור.
                        אני לא יכול לעשות יותר מידי טסטים כי זה נועל את הטבלה.

                        WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

                        תגובה 1 תגובה אחרונה
                        0
                        • dovidD מנותק
                          dovidD מנותק
                          dovid
                          ניהול
                          כתב ב נערך לאחרונה על ידי
                          #12

                          אינדקס על end_ts לדעתי לא יאט את ההכנסות משמעותית,
                          אבל זו אינטואיציה נטו,, אני די חלש במסדי נתונים.

                          • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
                          • בכל נושא אפשר ליצור קשר dovid@tchumim.com
                          תגובה 1 תגובה אחרונה
                          1
                          • dovidD מנותק
                            dovidD מנותק
                            dovid
                            ניהול
                            כתב ב נערך לאחרונה על ידי
                            #13

                            לפי הפלט שהבאת יש כמעט מיליון שורות מתאימות ליום בודד, זה הגיוני?

                            • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
                            • בכל נושא אפשר ליצור קשר dovid@tchumim.com
                            WWWW תגובה 1 תגובה אחרונה
                            0
                            • dovidD dovid

                              לפי הפלט שהבאת יש כמעט מיליון שורות מתאימות ליום בודד, זה הגיוני?

                              WWWW מנותק
                              WWWW מנותק
                              WWW
                              כתב ב נערך לאחרונה על ידי
                              #14

                              @dovid כתב בייעול שאילתת MYSQL:

                              לפי הפלט שהבאת יש כמעט מיליון שורות מתאימות ליום בודד, זה הגיוני?

                              לא.

                              WWW.netfree@gmail.com || קשבק! החזר כספי לבנק על רכישות באינטרנט || עונים על סקרים ומרוויחים כסף!

                              תגובה 1 תגובה אחרונה
                              0
                              • dovidD מנותק
                                dovidD מנותק
                                dovid
                                ניהול
                                כתב ב נערך לאחרונה על ידי
                                #15

                                @WWW סליחה, אני לא יודע לקרוא טוב את הEXPLAIN, כנראה זה 11 אלף בלבד.

                                • מנטור אישי בתכנות והמסתעף – להתקדם לשלב הבא!
                                • בכל נושא אפשר ליצור קשר dovid@tchumim.com
                                תגובה 1 תגובה אחרונה
                                1
                                תגובה
                                • תגובה כנושא
                                התחברו כדי לפרסם תגובה
                                • מהישן לחדש
                                • מהחדש לישן
                                • הכי הרבה הצבעות


                                בא תתחבר לדף היומי!
                                • התחברות

                                • אין לך חשבון עדיין? הרשמה

                                • התחברו או הירשמו כדי לחפש.
                                • פוסט ראשון
                                  פוסט אחרון
                                0
                                • דף הבית
                                • קטגוריות
                                • פוסטים אחרונים
                                • משתמשים
                                • חיפוש
                                • חוקי הפורום