Вопросы по use index и оптимизации sql

[Василий М.]

Привет всем.
Есть запрос:

SELECT IF (`advert_advert`.`advert_vip_date` IS NOT NULL AND `advert_advert`.`advert_vip_date` > NOW(), 1, 0) AS is_vip, `advert_advert`.`id`, `advert_advert`.`advert_vip_date`, `advert_advert`.`advert_id_user`, `advert_advert`.`advert_unique_user_cookie_id`, `advert_advert`.`advert_type`, `advert_advert`.`advert_category`, `advert_advert`.`advert_header`, `advert_advert`.`advert_price`, `advert_advert`.`advert_price_type`, `advert_advert`.`advert_free`, `advert_advert`.`advert_create_date`, `advert_advert`.`advert_text`, `category_category`.`id`, `category_category`.`category_name`, `category_category`.`category_url`, `user_country`.`id`, `user_country`.`country_name_ru`, `user_region`.`id`, `user_region`.`id_country`, `user_region`.`region_name_ru`, `user_city`.`id`, `user_city`.`id_region`, `user_city`.`id_country`, `user_city`.`city_name_ru` FROM `advert_advert` LEFT JOIN `category_category` ON `advert_advert`.`advert_category` = `category_category`.`id` LEFT JOIN `user_country` ON `advert_advert`.`advert_place_country` = `user_country`.`id` LEFT JOIN `user_region` ON `advert_advert`.`advert_place_region` = `user_region`.`id` LEFT JOIN `user_city` ON `advert_advert`.`advert_place_city` = `user_city`.`id` LEFT JOIN `user_user` ON `user_user`.`id` = `advert_advert`.`advert_id_user` WHERE `advert_category` IN ("2", "4", "5", "6", "7", "8", "9", "101", "127", "150", "180", "200", "201", "204", "208", "211", "218", "219", "237", "238", "239", "240", "241", "296", "297", "340", "341", "345", "346", "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", "380", "381", "382", "383", "384", "385", "386", "387", "388", "389", "390", "391", "392", "393", "396", "397", "398", "399", "400", "401", "402", "403", "404", "405", "406", "408", "409", "410", "411", "412", "414", "415", "416", "417", "418", "419", "420", "421", "422", "423", "424", "425", "426", "427", "428", "429", "430", "431", "432", "433", "434", "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", "461", "462", "463", "464", "465", "466", "467", "468", "469", "470", "471", "472", "473", "474", "475", "476", "477", "478", "479", "480", "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", "527", "528", "529", "530", "531", "532", "533", "534", "535", "536", "537", "538", "539", "540", "541", "542", "543", "544", "545", "546", "547", "548", "549", "550", "551", "552", "553", "554", "555", "556", "557", "558", "559", "560", "561", "562", "563", "564", "565", "566", "567", "568", "569", "570", "571", "572", "573", "574", "575", "576", "577", "578", "579", "580", "581", "582", "583", "584", "585", "586", "587", "588", "589", "590", "591", "592", "593", "594", "595", "596", "597", "598", "599", "600", "601", "602", "603", "604", "605", "606", "607", "608", "609", "610", "611", "612", "613", "614", "615", "616", "617", "618", "619", "620", "621", "622", "623", "624", "625", "626", "627", "628", "629", "630", "631", "632", "633", "634", "635", "636", "637", "638", "639", "640", "641", "642", "643", "644", "645", "646", "647", "648", "649", "650", "651", "652", "653", "654", "655", "656", "657", "658", "659", "660", "661", "662", "663", "664", "665", "666", "667", "668", "669", "670", "671", "672", "673", "674", "675", "676", "677", "678", "679", "680", "681", "682", "683", "684", "685", "686", "687", "688", "689", "690", "691", "692", "693", "694", "695", "696", "697", "698", "699", "700", "701", "702", "703", "704", "705", "706", "707", "708", "709", "710", "711", "712", "713", "714", "715", "716", "717", "718", "719", "720", "721", "722", "723", "724", "725", "726", "727", "728", "729", "730", "731", "732", "733", "734", "735", "736", "737", "738", "739", "740", "741", "742", "743", "744", "745", "746", "883", "884", "885", "886", "887", "888", "889", "890", "892", "893", "894", "896", "897", "898", "899", "900", "901", "902", "903", "904", "916", "917", "918", "919", "920", "921", "922", "923", "924", "925", "926", "927", "928", "929", "930", "931", "932", "933", "934", "935", "936", "937", "938", "939", "940", "941", "942", "943", "944", "945", "946", "947", "948", "953", "955", "956", "957", "958", "959", "960", "961", "962", "963", "964", "965", "966", "967", "969", "970", "971", "1085", "1086", "1087", "1088", "1089", "1090", "1091", "1092", "1094", "1095", "1096", "1097", "1098", "1099", "1141", "1142", "1149", "1150", "1151", "1152", "1153", "1154", "1155", "1156", "1157", "1158", "1159", "1160", "1162", "1163", "1164", "1165", "1166", "1167", "1168", "1169", "1170", "1171", "1172", "1173", "1174", "1175", "1176", "1177", "1178", "1179", "1180", "1181", "1182", "1183", "1184", "1185", "1186", "1187", "1188", "1189", "1190", "1191", "1192", "1193", "1194", "1195", "1196", "1197", "1198", "1199", "1200", "1201", "1202", "1203", "1204", "1205", "1206", "1207", "1208", "1209", "1210", "1211", "1212", "1213", "1214", "1215", "1216", "1217", "1218", "1219", "1220", "1221", "1222", "1223", "1224", "1225", "1226", "1227", "1228", "1229", "1233", "1234", "1236", "1237", "1238", "1239", "1240", "1242", "1243", "1244", "1245", "1246", "1247", "1248", "1249", "1250", "1251", "1252", "1253", "1254", "1255", "1256", "1257", "1258", "1259", "1260", "1261", "1262", "1263", "1264", "1265", "1266", "1267", "1268", "1269", "1270", "1271", "1272", "1273", "1274", "1275", "1276", "1277", "1278", "1279", "1280", "1281", "1282", "1283", "1284", "1285", "1286", "1287", "1288", "1289", "1290", "1291", "1292", "1293", "1294", "1295", "1296", "1297", "1298", "1299", "1300", "1301", "1302", "1303", "1304", "1305", "1306", "1307", "1308", "1309", "1310", "1311", "1312", "1313", "1314", "1315", "1316", "1318", "1319", "1320", "1321", "1322", "1323", "1324", "1325", "1326", "1327", "1328", "1329", "1330", "1331", "1332", "1333", "1334", "1335", "1336", "1337", "1338", "1339", "1340", "1341", "1343", "1344", "1345", "1346", "1347", "1348", "1") AND `advert_active` = 1 AND (`user_user`.`id` > 0 AND `user_user`.`user_active` OR `user_user`.`id` = -1) ORDER BY `is_vip` DESC, `advert_advert`.`advert_create_date` DESC LIMIT 0, 20

жирным выделил только то, что нам интересно. Не будем спрашивать, почему и откуда этот адский ад берется.

Индекс создал на поле advert_category и advert_active.
Вопроеки моим ожиданиям mysql индекс не использует и explain показывает, что затрагивается все 12000 записей из базы:


пишу use index и уже типа лучше:



Почему объявленный мной индекс mysql без use index не использует?

 

[c0dex]

Мускуль тупой и выбрал самый короткий ключ и все дела. Предсказуемо, очень.

 

[Активист]

Потому что MySQL решает нужно ли использовать индексы или проще пройтись по таблице. Если бы у вас было 1М записей то скорее всего индексы бы SQL оптимизатор заюзал, а так у вас же только using where и using filesort (т.е. временных таблиц на диске не создавались) и я думаю оба SQL выполняются с одной скорость.

 

[Активист]

http://habrahabr.ru/post/154167/

 

[Василий М.]

Активист, ну скорость с индексами 0.01, а без - 0.03

Ладно, хрен с ними, с индексами.
Давайте поговорим про оптимизацию этой структуры.
Короче говоря эта туча ID в запросе - это потомки одной большой категории.
В свое время (в 2009 году) я не заложил должного функционала и теперь у меня нет такого типа данных как "свойство", а естm просто большое дерево, которое будет ещё больше.

Мне категорически не нравится выборка элементов из категорий с помощью IN (1,2,3,....,100500)

Какие можно рассмотреть варианты решения?

 

[Активист]

1. Либо смириться
2. Либо использовать индекс вариаций
(как в движке этого форума используюется вариации прав пользователей). Т.е. вы создаете таблицу, где сохраняется возможные вариации. И добавляете еще одну таблицу, где создаете отношения "товар" => "есть в вариации". Потом делаете выборку по постоянному.

Типа "select ... where `variation` = '1356993';

3. Либо просто создать большую таблицу "ид" => "есть в категории". Обновлять каждый раз, когда нужно.

 

[Активист]

А да, еще можно в таблицу с товарами вставить данные по nested sets дереву категорий, по аналогии с http://phpclub.ru/faq/Tree/Ns

 

[Василий М.]

Активист, поясни по поводу 2,3

Вот добавился элемент в категорию.
Я так понимю вариация должна быть
id элемента => есть в категории1
id элемента => есть в категории1.1
id элемента => есть в категории1.1.2
и т.д.?

 

[Активист]

Не.. Я как понимаю вы чайлдов дергаете (т.е. селект из всех групп).

Нужно делать так.

По второму (меньше данных)
1. Вариация - группа содержащая группы 1,2,5,6,7,9,10 = вариация 112 (условно). Первоначально создать по всем, потом только добавлять.
2. Таблица отношений Вариация -> Товар (условно, вариация 112 товар 1, вариация 112 товар 223, вариация 112 товар 25;
3. select ... ... from `variation_to_product` where `variation` = '112';

По третьему (больше данных):
1. Обычная таблица, в которой перечислены все возможные товары в конкретной группе (
Пример:
Группа 1 товар 1
Группа 1 товар 2
Группа 1 товар 5
Группа 1 товар 6
Группа 1 товар 7
...
Далее (условно 2 - дочерняя группа группа группы 1, в которой мы найдем товары 2,5,6,7)
Группа 2 Товар 2
Группа 2 Товар 5
Группа 2 товар 6
Группа 2 товар 7

И так на все группы. Данных будет больше, но индекс будет использоваться без range.

(третий вариант проще).

 

[Активист]

Но судя по скорости -вам не надо ничего делать)) Поскольку MYSQL отрабатывает быстро. Это я вам в теории рассказал, как добиваются производительности на больших объемах данных.

 

[Василий М.]

Активист, спасибо, я думал что-то вариаций, только идея была несколько иная.
Я подумаю как лучше.
Проблема в том, что как группы создаются/удаляются/переносятся довольно часто, так и "товары" в них также.

 

[Активист]

Ну, можно nested sets вставить в таблицу товаров)) еще проще. плюс есть готовые классы.

 

[Василий М.]

Не.. не в этой жизни..

По третьему (больше данных):

В принципе это осуществимо. У меня раз в 15 минут обновляется информация о группах. Каждая группа знает всех своих потомков - денормализация по крону происходит, что бы рекурсивно потомков каждой группы не искать.
В принципе можно написать решение, опять же по крону, которое будет брать элемент группы (товар), находить ему принадлежащую группу и писать это в таблицу связей. Только запрос все равно будет вида select ... from group_to_elements where group in (1,2,3,,,,100500) + join к таблице элементов будет

 

[Активист]

почему where group in ? вы избыточно делаете... Т.е. вы же потомков пересобираете, ну добавьте еще таблицу (а можно и туда, где потомки) куда воткнете все возможные товары для группы (и каждой вложенной). Что мешает?

 

[Активист]

Джойн по индексу ничем не плох.

 

[Активист]

Кстати. таблицу лучше делать innodb и start transaction | commit | rollback чтобы ускорить загрузку данных при обновлении.

 

[HraKK]

Джойн по индексу ничем не плох.

Конечно. Только убил шардирование)