MySql JOIN עם אינדקס לוקח המון זמן
-
טבלת products
id INT, PRIMARY productId: VARCHAR, UNIQUE name: TEXT, NO INDEXטבלת products_users
id INT, PRIMARY userId: INT INDEX productId: VARCHAR, INDEXהשאילתה:
SELECT products_users.id, products_users.userId, products_users.productId, products.name AS name FROM products_users JOIN products ON products.productId=products_users.productId WHERE userId=123שאילתה כזו לוקחת יותר מעשר שניות.
אם אני מוריד את הproducts.name AS nameזה נגמר בפחות משניה, למרות שאני משאיר את ה JOIN!
כשאני עובד מול כל טבלה בנפרד, התגובה מהירה מאד, רק כשאני מצרף אותם לשאילתה אחת ושולף את עמודת הטקסט זה נתקע
מה הסיבה? -
טבלת products
id INT, PRIMARY productId: VARCHAR, UNIQUE name: TEXT, NO INDEXטבלת products_users
id INT, PRIMARY userId: INT INDEX productId: VARCHAR, INDEXהשאילתה:
SELECT products_users.id, products_users.userId, products_users.productId, products.name AS name FROM products_users JOIN products ON products.productId=products_users.productId WHERE userId=123שאילתה כזו לוקחת יותר מעשר שניות.
אם אני מוריד את הproducts.name AS nameזה נגמר בפחות משניה, למרות שאני משאיר את ה JOIN!
כשאני עובד מול כל טבלה בנפרד, התגובה מהירה מאד, רק כשאני מצרף אותם לשאילתה אחת ושולף את עמודת הטקסט זה נתקע
מה הסיבה?@יוסף-בן-שמעון אתה יכול להביא פלט של EXPLAIN עבור שתי השאילתות?
אגב, האם זה קורה על שרת לוקלי או מרוחק?
-
טבלת products
id INT, PRIMARY productId: VARCHAR, UNIQUE name: TEXT, NO INDEXטבלת products_users
id INT, PRIMARY userId: INT INDEX productId: VARCHAR, INDEXהשאילתה:
SELECT products_users.id, products_users.userId, products_users.productId, products.name AS name FROM products_users JOIN products ON products.productId=products_users.productId WHERE userId=123שאילתה כזו לוקחת יותר מעשר שניות.
אם אני מוריד את הproducts.name AS nameזה נגמר בפחות משניה, למרות שאני משאיר את ה JOIN!
כשאני עובד מול כל טבלה בנפרד, התגובה מהירה מאד, רק כשאני מצרף אותם לשאילתה אחת ושולף את עמודת הטקסט זה נתקע
מה הסיבה?@יוסף-בן-שמעון עצם העובדה שהכללת הname משנה היא צפויה ומובנת לגמרי.
איך עובד אינדקס לא ראשי? יש טבלה נפרדת, בה מאוחסנים הערכים של האינדקס (productId) לפי סדר, וליד כל אחד יש מצביע למיקום בטבלה המקורית. איתור האינדקס הוא מאוד מהר (נניח חיפוש בינארי) אבל אחרי האיתור יש לנו ביד רק את ערך האינדקס עצמו שזה הproductId, בשביל לדעת את הname אנחנו צריכים לעקוב אחרי היעד בטבלת המקור.
זה סיבת הבדל הזמן, ולכן נקודה זו לא עומדת במרכז השאלה. מרכז השאלה עובר לכמות הזמן - כמובן שזה לא אמור לקחת 10 שניות!
עשר שניות לאיתור רשומה שיודעים את מיקומה זה להשערתי נובע מטבלה במצב לא מתוחזק (למשל אחרי מחיקה של הרבה שורות בכל מיני מקומות וכדומה) אולי פקודת OPTIMIZE לטבלה תשנה את המצב דרמטית.
(בחזרה לנושא האינדקס לא ראשי, במידה והדוגמה היא אכן המציאות וכל מטרת האינדקס הוא תמיד תמיד להביא את השם, אפשר להורות לMYSQL לכלול בטבלת האינדקס ישירות את שדה הname.
קוראים לזה covering index. במקרה כזה בעצם הMYSQL לא יפתח פיזית בכלל את טבלת המוצרים). -
@יוסף-בן-שמעון אתה יכול להביא פלט של EXPLAIN עבור שתי השאילתות?
אגב, האם זה קורה על שרת לוקלי או מרוחק?
@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אתה יכול להביא פלט של EXPLAIN עבור שתי השאילתות?
עם שליפה של העמודה הטקסטואלית:
בלי שליפה
זה אומר שבשאילתה האיטית הוא בכלל לא משתמש באינדקס?@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אגב, האם זה קורה על שרת לוקלי או מרוחק?
גם וגם
-
@יוסף-בן-שמעון עצם העובדה שהכללת הname משנה היא צפויה ומובנת לגמרי.
איך עובד אינדקס לא ראשי? יש טבלה נפרדת, בה מאוחסנים הערכים של האינדקס (productId) לפי סדר, וליד כל אחד יש מצביע למיקום בטבלה המקורית. איתור האינדקס הוא מאוד מהר (נניח חיפוש בינארי) אבל אחרי האיתור יש לנו ביד רק את ערך האינדקס עצמו שזה הproductId, בשביל לדעת את הname אנחנו צריכים לעקוב אחרי היעד בטבלת המקור.
זה סיבת הבדל הזמן, ולכן נקודה זו לא עומדת במרכז השאלה. מרכז השאלה עובר לכמות הזמן - כמובן שזה לא אמור לקחת 10 שניות!
עשר שניות לאיתור רשומה שיודעים את מיקומה זה להשערתי נובע מטבלה במצב לא מתוחזק (למשל אחרי מחיקה של הרבה שורות בכל מיני מקומות וכדומה) אולי פקודת OPTIMIZE לטבלה תשנה את המצב דרמטית.
(בחזרה לנושא האינדקס לא ראשי, במידה והדוגמה היא אכן המציאות וכל מטרת האינדקס הוא תמיד תמיד להביא את השם, אפשר להורות לMYSQL לכלול בטבלת האינדקס ישירות את שדה הname.
קוראים לזה covering index. במקרה כזה בעצם הMYSQL לא יפתח פיזית בכלל את טבלת המוצרים).@dovid כתב בMySql JOIN עם אינדקס לוקח המון זמן:
למשל אחרי מחיקה של הרבה שורות בכל מיני מקומות וכדומה
מה המשמעות של מחיקת שורות? למה זה מזיק לטבלה?
-
@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אתה יכול להביא פלט של EXPLAIN עבור שתי השאילתות?
עם שליפה של העמודה הטקסטואלית:
בלי שליפה
זה אומר שבשאילתה האיטית הוא בכלל לא משתמש באינדקס?@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אגב, האם זה קורה על שרת לוקלי או מרוחק?
גם וגם
אני לא יודע מה לומר על הEXPLAIN, אם אני מבין בכלל מה יש שמה זה נראה שבהשמטת הname הוא מתחיל עם הproduct_users ורק משלים את הצריך מאינדקס הproducts, אבל בהכללת הname הוא מתחיל הפוך שזה הזוי (כי אין סינון לטבלת הproducts).
אשמח מאוד למי שיסביר את התוצאה הזו ואיך להינצל מכזה דבר.(לגבי מחיקה אני המצאתי משהו, בגלל שזכור לי שבמקרה כזה נוצרים רווחים ואולי זה פוגע ביעילות האינדקס, מאוד יכול להיות שזה קשקוש מוחלט. מה שברור שיש מקרים שהטבלה לא מסודרת פיזית בדיסק טוב וזה אולי גם פוגע ביעילות אינדקסים).
-
@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אתה יכול להביא פלט של EXPLAIN עבור שתי השאילתות?
עם שליפה של העמודה הטקסטואלית:
בלי שליפה
זה אומר שבשאילתה האיטית הוא בכלל לא משתמש באינדקס?@yossiz כתב בMySql JOIN עם אינדקס לוקח המון זמן:
אגב, האם זה קורה על שרת לוקלי או מרוחק?
גם וגם
@יוסף-בן-שמעון האם אתה יכול לעזור לי לעשות דוגמה לבעיה? כי עשיתי פידל ולא הצלחתי לקבל את התוכנית שאתה קיבלת
-
@יוסף-בן-שמעון האם אתה יכול לעזור לי לעשות דוגמה לבעיה? כי עשיתי פידל ולא הצלחתי לקבל את התוכנית שאתה קיבלת
התודה והברכה ל @yossiz שכמנהגו הטוב טרח גם בפרטי המשיך לנסות להבין את הבעיה ולא נח ולא שקט עד אם כילה הדבר, אבל לצערי לא הצלחתי לייצר שחזור מינימלי של הבעיה, אנסה עוד לדבג ואם יהיו לי חידושים שיכולים לעניין מישהו אעדכן כאן בלי נדר
(הזוי שאם אני מפריד את השאילתות, קודם שולף יוזרים ואחר כך מוצרים עם where in, ומאחד אותם בעצמי, זה לוקח בערך עשירית שניה, לעומת שאילתה מאוחדת שלוקחת בערך עשר שניות, עקוב mysql ואנוש מי ידענו...)
-
