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

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

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

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

מתוזמן נעוץ נעול הועבר תכנות
15 פוסטים 3 כותבים 456 צפיות 3 עוקבים
  • מהישן לחדש
  • מהחדש לישן
  • הכי הרבה הצבעות
תגובה
  • תגובה כנושא
התחברו כדי לפרסם תגובה
נושא זה נמחק. רק משתמשים עם הרשאות מתאימות יוכלו לצפות בו.
  • 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
                              • דף הבית
                              • קטגוריות
                              • פוסטים אחרונים
                              • משתמשים
                              • חיפוש
                              • חוקי הפורום