SQL שאילתה תקועה... מה כן אפשר?

[ש.ב.ח.]

השאילתה הבאה נתקעת לדקות ארוכות ללא מתן תשובה.

select
    `records`.*,
    `data0`.`value` as `laravel_through_key`
from
    `records`
    inner join `meta_records` as `data2` on `data2`.`record_id` = `records`.`id`
    inner join `meta_records` as `data1` on `data1`.`record_id` = `data2`.`value`
    inner join `meta_records` as `data0` on `data0`.`record_id` = `data1`.`value`
where
    (
        `data0`.`field_id` = 5
        and `data0`.`field_type` = 'App\\Models\\Field'
    )
    and (
        `data1`.`field_id` = 7
        and `data1`.`field_type` = 'App\\Models\\Field'
    )
    and (
        `data2`.`field_id` = 16
        and `data2`.`field_type` = 'App\\Models\\Field'
    )
     and (
        (
            exists (
                select
                    *
                from
                    `meta_records` as `meta_exists`
                where
                    `meta_exists`.`field_id` = 10
                    and `meta_exists`.`value` > 1000
                    and `meta_exists`.`record_id` = `data1`.`record_id`
            )
        )
    )
    and `data0`.`value` in (
        4017,
        4018,
        4019,
        4020,
        4021,
        4022,
        4023,
        4024,
        4025,
        4026,
        4027,
        4028,
        4029,
        4030,
        4031,
        4032,
        4033,
        4034,
        4035,
        4036,
        4037,
        4038,
        4039,
        4040,
        4041,
        4042,
        4043,
        4044,
        4045,
        4046,
        4047,
        4048,
        4049,
        4050,
        4051,
        4052,
        4053,
        4054,
        4055,
        4056,
        4057,
        4058,
        4059,
        4060,
        4061,
        4062,
        4063,
        4064,
        4065,
        4066,
        4067,
        4068,
        4069,
        4070,
        4071,
        4072,
        4073,
        4074,
        4075,
        4076,
        4077,
        4078,
        4079,
        4080,
        4081,
        4082,
        4083,
        4084,
        4085,
        4086,
        4087,
        4088,
        4089,
        4090,
        4091,
        4092,
        4093,
        4094,
        4095,
        4096,
        4097,
        4098,
        4099,
        4100,
        4101,
        4102,
        4103,
        4104,
        4105,
        4106,
        4107,
        4108,
        4109,
        4110,
        4111,
        4112,
        4113,
        4114,
        4115,
        4116
    )

ללא הקטע הבא (מתוך השאילתה הקודמת):

and (
        (
            exists (
                select
                    *
                from
                    `meta_records` as `meta_exists`
                where
                    `meta_exists`.`field_id` = 10
                    and `meta_exists`.`value` > 1000
                    and `meta_exists`.`record_id` = `data1`.`record_id`
            )
        )
    )

השאילתה חוזרת תוך 230ms עם 823 תוצאות.

מה הדרך לנטרל את הכלב השחור??? או יותר נכון מה כך כף מציק לו שהוא נתקע...?

[clickone]

@ש-ב-ח הקטע השני שהוצאת, והוצאתו גורמת לשאילתא לרוץ מהר, הוא בעצם שאילתת משנה, שגורמת לו לחפש על כל רשומה את ההתניה בטבלה meta_records, וזה הגיוני שלוקח לו זמן. (בעיקר אם יש בmeta_records הרבה רשומות)

פתרונות אפשריים:

1. לשים אינדקס בטבלה meta_records על השדות שאתה מפלטר, עדיף אינדקס אחד על כל 3 השדות ביחד.
2. אם אתה מסנן בטבלת המשנה משהו קבוע, אז להוסיף עמודה בטבלה הראשית שתכיל כן/לא, ופשוט לעדכן את זה בשעת ההכנסה של הנתונים.
   אני מדגיש שהשיטה הזו לא נכונה מבחינת תיכנון נכון של DB (אחת הסיבות כי אתה יכול לחשב את זה, ואסור עקרונית לשמור נתון סטטי כשאתה יכול לחשב אותו), אבל במקרה של בעית ביצועים כזו אני אישית מאד בעד לשמור גם את הנתון סטטי ברשומה עצמה, ולא לחשב אותה כל פעם מחדש.
3. אפשרות נוספת ששווה לבדוק (לא בטוח שזה אפשרי, אבל תנסה), זה לא לשים את החיפוש של הטבלה השניה כשאילתת משנה, אלא לחבר אותה כJOIN, ואז לסנן את כל מה שמתאים (ממילא אתה מחפש את מה שקיים ולא מה שחסר, כך שלכאורה ייתכן שאתה יכול להשתמש אפילו בINNER JOIN בלב שלם)

[ש.ב.ח.]

@clickone אמר בSQL שאילתה תקועה... מה כן אפשר?:

(בעיקר אם יש בmeta_records הרבה רשומות)

אכן בטבלה זו יש מיליוני שורות

---

@clickone אמר בSQL שאילתה תקועה... מה כן אפשר?:

לשים אינדקס בטבלה meta_records על השדות שאתה מפלטר, עדיף אינדקס אחד על כל 3 השדות ביחד.

כמה זה יכול להפריע בעת הכנסה או מחיקת שורה?

---

@clickone אמר בSQL שאילתה תקועה... מה כן אפשר?:

אם אתה מסנן בטבלת המשנה משהו קבוע, אז להוסיף עמודה בטבלה הראשית שתכיל כן/לא, ופשוט לעדכן את זה בשעת ההכנסה של הנתונים.
אני מדגיש שהשיטה הזו לא נכונה מבחינת תיכנון נכון של DB (אחת הסיבות כי אתה יכול לחשב את זה, ואסור עקרונית לשמור נתון סטטי כשאתה יכול לחשב אותו), אבל במקרה של בעית ביצועים כזו אני אישית מאד בעד לשמור גם את הנתון סטטי ברשומה עצמה, ולא לחשב אותה כל פעם מחדש.

זה בדיוק מה שאני עושה, כאן מדובר בשלב שהוא אוסף את הנתון הצבירתי ע"מ לאחסן אותו כערך מטה לשורה המקורית.

@clickone אמר בSQL שאילתה תקועה... מה כן אפשר?:

אפשרות נוספת ששווה לבדוק (לא בטוח שזה אפשרי, אבל תנסה), זה לא לשים את החיפוש של הטבלה השניה כשאילתת משנה, אלא לחבר אותה כJOIN, ואז לסנן את כל מה שמתאים (ממילא אתה מחפש את מה שקיים ולא מה שחסר, כך שלכאורה ייתכן שאתה יכול להשתמש אפילו בINNER JOIN בלב שלם)

נוסה, חזר אחרי 300ms

[שמואל4]

צריך לקחת בחשבון שהוספת אינדקס לטבלא בשימוש בעיקר כזו גדולה יכולה לקחת הרבה זמן - וזה כולל נעילה של הטבלא.

[clickone]

@ש-ב-ח אמר בSQL שאילתה תקועה... מה כן אפשר?:

נוסה, חזר אחרי 300ms

אז מה מפריע לך להשתמש בJOIN במקום שאילתת משנה? זה די מהיר לפי איך שאתה מתאר....

@שמואל4 אמר בSQL שאילתה תקועה... מה כן אפשר?:

צריך לקחת בחשבון שהוספת אינדקס לטבלא בשימוש בעיקר כזו גדולה יכולה לקחת הרבה זמן - וזה כולל נעילה של הטבלא.

צודק לגמרי.
בכל מקרה אצלי באחד הפרוייקטים, באחת הטבלאות, עם כ50 מליון שורות, יש אינדקסים ובינתיים ב"ה עובד די טוב ומהיר. (האמת שאצלי זה MSSQL, וכאן נראה שזה MYSQL, אבל לדעתי לא אמורים להיות הבדלים משמעותיים)
שווה בכל מקרה לנסות.
ייתכן אם זה אפשרי שלפחות את הגדרת האינדקס הראשוני לא כדאי לעשות כשהDB בפרודקשיין.

[Y.Excel.Access]

@ש-ב-ח אמר בSQL שאילתה תקועה... מה כן אפשר?:

    (
        exists (
            select
                *

לא הבנתי למה צריך לשלוף את הנתונים בSELECT, הלא זו בדיקה אם EXISTS בלבד. תכתוב

SELECT 0

או SELECT "" וכד' (לא לקרוא לשום נתון מתוך הטבלה). כך זה יחסוך לך את הקריאה שוב ושוב מתוך השדות שאינה נצרכת.
(כי אם ה WHERE לא נכון לא יחזור כלום. וק"ל)

[dovid]

@Y-Excel-Access ההערה שלך נכונה תיאורטית, אבל בפועל זה לא משנה כלום. כי:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

זה מהתיעוד של mysql.