שלום יש לי טבלה במסד הנותנים MYSQL שיש לה כ500 אלף רשומות וכל יום נוסף עוד כ100 רשומות
בטבלה יש לי עמודה X שמכילה מספרים מסויימים כמו [1][2][3]
בניתי שאילתא שמחפשת מספר מסויים

קוד PHP:

 SELECT `id` FROM `table` WHERE `x` LIKE '%[1]%' 


עכשיו בגלל שיש הרב רשומות לוקח לי הרבה זמן להריץ את העמוד.
עכשיו אני צריך לחפש את הנתון הזה רק ב200 השורות האחרונות..
איך אני עושה את זה? ואיך ניתן ליעל את השאילתא?

האם ככה?

קוד PHP:

 SELECT `id` FROM `table` WHERE `x` LIKE '%[1]%' ORDER BY `id` DESC LIMIT 0,200 


במקום FROM TABLE תעשה SUBSELECT ששולף רק את ה 200 האחרונות...

נמאס לכם לזכור סיסמאות? לחצו כאן!

כיצד בנית את האינדקסים אצלך..?

דבר ראשון - אפילו אם יש לך אינדקס על השדה x, אתה לא מנצל אותו כשהביטוי LIKE מכיל את סימן האחוז ( % ) בתחילת מחרוזת החיפוש. זה גורם ל-mysql לבצע filesort, כלומר חיפוש בטבלה כולה ללא שימוש באינדקס...

כדי לבצע חיפוש ב-200 הרשומות האחרונות יש לך 3 אופציות בהתאם לאינדקסים הזמינים:

1. אם יש לך את האינדקס הבא: (id,x), סימן שאתה יכול לבצע את החיפוש שכתבת ביעילות עם השאילתא הבאה:

קוד:

SELECT `id` FROM `table` WHERE `x` LIKE '[1]%' ORDER BY `id` DESC LIMIT 0,200 

זה אמור להיות הדרך היעילה ביותר כיוון שאתה משתמש באינדקס עבור כל השאילתא - אתה לא ניגש לטבלה כדי להחזיר מידע ללקוח. (בשאילתא אתה עושה שימוש רק בטורים id ו- x שמהן האינדקס בנוי)

2. אתה יכול לעשות שימוש בשאילתא הבאה:

קוד:

SELECT `id` FROM `table` WHERE `x` LIKE '[1]%' ORDER BY `id` DESC LIMIT 0,200 

כאשר יש לך את האינדקסים הבאים: (x), (id)
mysql אמור לבצע intersection עם התוצאות שהוא ייקח מכל אינדקס.
אבל אם ה-cardinality של האינדקסים (ייחודיות הערכים באינדקסים) הוא נמוך, זה גורם ל-mysql לקבל הרבה תוצאות, מה שאומר שהשאילתא לא תהיה יעילה במיוחד כמו שאולי תצפה.

3. לבצע את מה ששימי אמר ולהריץ שאילתת subquery ששולפת את 200 הרשומות האחרונות בהתאם ל-sorting שתבצע על הטור id, ומהתוצאות של השאילתא הזו תבצע חיפוש על הטור x.

-----
כדי לדעת איזו שאילתא הכי יעילה אתה חייב לבצע בדיקות...
אגב לא ברור לי למה הוספת סוגריים מרובעים ( [] ) מסביב למספרים בטור x, זה נראה לי ממש מיותר, היית יכול להשתמש בפסיקים כדי להפריד ביניהם..

אבל בשורה X יכול להיות המחזורת הבאה
[1][2][45][78]

אז הביוטוי

קוד PHP:

 `x` LIKER '[45]%' 


לא יתפוס לא?

בקיצור תעשה SUBSELECT כמו שהצעתי בהתחלה.

נמאס לכם לזכור סיסמאות? לחצו כאן!

נכון, הוא לא יתפוס.
נסה לחשוב על שינוי מבנה הטבלאות שלך אם זה אפשרי, כי לבצע filesort זה ממש לא מתאים.

בעמודה X יכול להיות מספרים מכלום עד 20 מספרים יש לך רעיון לדרך איך לעשות את זה יותר טוב?

לפי מה שאני מבין הפיתרון הטבעי הוא להעביר את הערכים של השדה x לטבלה נוספת ולבצע join בין הטבלאות.

יענו במקום שיהיה בשדה x את ערכים כמו "[1][5][6]", תשים כל מספר ברשומה משל עצמו בטבלה חדשה\אחרת.
אבל כדאי לך לנסות גם את מה ששימי כתב, לראות מה עדיף מבחינת ביצועים.