Полная Версия: MySql составной индекс для запросов вида IN
| Цитата |
| В том числе и сам запрос можно выложить. |
SELECT d.id, d.ip, d.p, d.uid, d.cat, d.region, d.region2, d.region3, d.date_add, d.user_id, d.status, d.email, d.user, d.title, d.text, d.f_94 images, d.active, d.counter, d.date_del, d.nosend, d.subdomain ,(SELECT count(1) FROM
sait_comments WHERE message_id = d.id) comments_count FROM sait_db as d WHERE status = 1 AND
cat in (630,359,611,534,541,535,538,531,536,637,533,642,634,603,537,532,539,631,636,360,622,627,540,639,638,605,646,516,617,526,519,522,602,606,518,615,608,604,525,601,641,527,524,523,640,635,633,361,517,358)
AND region IN (126,127,661,662,663,128,380,381,382,383,384,385,386,387,388,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,129,157,158,159,160,161,162,163,164,165,166,167,130,168,169,170,171,172,173,174,175,
176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,131,196,197,198,199,200,201,202,203,204,205,206,207,208,209,132,210,211,212,213,214,215,216,217,218,219,220,133,221,222,223,224,22
5,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,134,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,135,156,272,273,274,
275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,136,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,137,155,664,665,666,667,669,670,671,672,681,682,683,684,
685,694,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,6627,6639,138,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,
359,
360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,139,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,6683211,
6683212,6683213,6683214,6683215,6683216,6683217,6683218,6683219,6683220,6683221,6683222,6683223,6683224,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,
460,140,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,141,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,
510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,142,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,143,548,549,550,551,552,553,554,555,556,557,558,559,
560,561,562,563,564,565,566,697,6683210,144,567,568,569,570,571,572,573,574,575,576,577,145,578,579,580,581,582,583,584,585,586,587,588,589,6626,6633,6649,146,590,591,592,593,594,595,596,597,598,599,600,147,
601,602,603,604,605,606,607,608,609,610,611,6628,6637,6650,6651,148,612,613,614,615,616,617,618,695,6632,149,619,620,621,622,623,624,625,842,150,626,627,628,629,630,631,632,633,634,843,151,635,636,637,638,639,
674,675,676,677,678,679,680,6630,152,640,641,642,643,644,645,646,844,6634,6635,153,647,648,649,650,651,652,653,654,655,656,657,658,659,660,6629,6631,6640,6641,6642,6643,6644,6645,6646,6647,6648,696,6638,389,
6652) ORDER by raised desc, date_add desc LIMIT 0, 30
| Цитата (Serg86 @ 17.12.2015 - 09:25) |
| Через какоето время запускаю запрос снова и он выполняется за 15 секунд, тут же повторяю запрс и получаю 0,1 секунду, в чем прикол не пойму. |
это называется кеширование на уровне базы данных
скажу честно, я на 90% уверен, что ошибка у вас на уровне логики еще до самого запроса, отсюда и рога с копытами (и собственно такой неудобоваримый запрос), но данная задача выходит за рамки бесплатной помощи на уровне форума
могу только намекнуть
| Цитата (sergeiss @ 14.12.2015 - 18:28) |
| возможно, тут получится сделать джойны |
| Цитата |
| у меня бы точно они получились |
Киньте в личку сроки и стоимость, обсудим
Serg86, в начале темы в условии у тебя фигурировали только cat & region. А теперь еще и status, оказывается, присутствует. Сделай комбинированный индекс по всем этим 3-м параметрам одновременно.
И я так и не понял до конца логику построения данных. Например, является ли уникальным регион или уникальна пара "категория-регион", а индексы регионов могут повторяться для разных категорий. От этого очень зависит, как построить запрос. И, возможно, как построить таблицы.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
И я так и не понял до конца логику построения данных. Например, является ли уникальным регион или уникальна пара "категория-регион", а индексы регионов могут повторяться для разных категорий. От этого очень зависит, как построить запрос. И, возможно, как построить таблицы.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
| Цитата |
| И я так и не понял до конца логику построения данных. Например, является ли уникальным регион или уникальна пара "категория-регион", а индексы регионов могут повторяться для разных категорий. От этого очень зависит, как построить запрос. И, возможно, как построить таблицы. |
Каждой строке присвоен свой регион и категория. В отдельной таблице хранится дерево регионов(region in (......)), в отдельной дерево категорий(cat in (......)). Соответственно они выбираются еще до запроса и подставляются в него. Тоесть любая строка может быть в регионе из списка и в категории из списка.
| Цитата |
| Сделай комбинированный индекс по всем этим 3-м параметрам одновременно |
Сделал, explain показывает что задействован только статус и категория.
| Цитата (Serg86 @ 17.12.2015 - 11:20) |
| Каждой строке присвоен свой регион и категория. |
Всё равно не понятно: индекс региона уникальная величина или может повторяться для разных категорий? Ну, то есть, могут ли быть такие пары категория-регион:
1-2
1-7
4-2
4-5
3-7
...
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
| Цитата |
| И я так и не понял до конца логику построения данных. Например, является ли уникальным регион или уникальна пара "категория-регион", а индексы регионов могут повторяться для разных категорий. От этого очень зависит, как построить запрос. И, возможно, как построить таблицы. |
Кинул вам в личку дамп и запрос, если интересно гляньте.
| Цитата |
| Всё равно не понятно: индекс региона уникальная величина или может повторяться для разных категорий? Ну, то есть, могут ли быть такие пары категория-регион: 1-2 1-7 4-2 4-5 3-7 ... |
Могут, любая строка теоретически может лежать в любом из перечисленных регионов, в том числе и 2 и более строк в одном регионе.
Попробуй выбрать только айдишники, при тех же условиях
Скорее всего, у тебя этот запрос будет выполняться быстро всегда. Если я угадал, то подскажу следующий шаг (если сам не догадаешься).
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
SELECT d.id
FROM sait_db as d
WHERE ....
Скорее всего, у тебя этот запрос будет выполняться быстро всегда. Если я угадал, то подскажу следующий шаг (если сам не догадаешься).
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
| Цитата |
| Попробуй выбрать только айдишники, при тех же условиях SELECT d.id FROM sait_db as d WHERE .... Скорее всего, у тебя этот запрос будет выполняться быстро всегда. Если я угадал, то подскажу следующий шаг (если сам не догадаешься). |
Попробывал, первый раз полторы секунды, потом примерно 0,1.
Как сказать запросу чтоб кеширование не использовал, чтобы реальную картину видеть?
| Цитата |
| (если сам не догадаешься). |
Подозреваю что тормоза в выборке по двум таблицам, но всётаки выскажите свои соображения. Кстати вторая таблица пустая.
Тогда так теперь попробуй:
Если в подзапросе немного строк на выходе, то суммарно будет не более тех же 1.5 секунд.
PS. И проверь, что у тебя есть индекс по полю message_id в таблице sait_comments
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
SELECT список_полей
FROM sait_d
WHERE id in (
SELECT d.id
FROM sait_db as d
WHERE d.status = 1 and cat in (...) and region in (...).
)
Если в подзапросе немного строк на выходе, то суммарно будет не более тех же 1.5 секунд.
PS. И проверь, что у тебя есть индекс по полю message_id в таблице sait_comments
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
Пробую вставить SQL_NO_CACHE после селекта, не помогает, первый раз запрос 14 секунд, второй 0,2. Сомневаюсь я что второй раз он его так быстро выполняет
Блин где логика, в кеше MYSQL пусто, проверил, время запроса 0,1 - 0,2 секунды, почему тогда запрос через время 17 секунд работает
| Цитата |
| SELECT список_полей FROM sait_d WHERE id in ( SELECT d.id FROM sait_db as d WHERE d.status = 1 and cat in (...) and region in (...). ) |
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Здесь расположена полная версия этой страницы.