При наличии конструкции
... IN (конкретные значения)
MySQL начинает долго выбирать индексы
Вот 3 примера:
Добавляем в таблицу (modx_site_tmplvar_contentvalues) 4 индекса:
ALTER TABLE `modx_site_tmplvar_contentvalues` ADD INDEX `idx_value` ( `value` ( 20 ) ) ALTER TABLE `modx_site_tmplvar_contentvalues` ADD INDEX `idx_tv_value` ( `tmplvarid` , `value` ( 20 ) ) ALTER TABLE `modx_site_tmplvar_contentvalues` ADD INDEX `idx_value_tv` ( `value` ( 20 ), `tmplvarid` ) ALTER TABLE `modx_site_tmplvar_contentvalues` ADD INDEX `idx_content_tv` ( `content`, `tmplvarid` )
Пример 1
SELECT DISTINCT `modx_site_tmplvar_contentvalues`.contentid AS id FROM `modx_site_tmplvar_contentvalues` WHERE ((`modx_site_tmplvar_contentvalues`.tmplvarid IN (208,209)) AND ((modx_site_tmplvar_contentvalues.value = '18') OR (modx_site_tmplvar_contentvalues.value = '11') OR (modx_site_tmplvar_contentvalues.value = '29') OR (modx_site_tmplvar_contentvalues.value = '60') OR (modx_site_tmplvar_contentvalues.value = '10') OR (modx_site_tmplvar_contentvalues.value = '20') OR (modx_site_tmplvar_contentvalues.value = '23') OR (modx_site_tmplvar_contentvalues.value = '22') OR (modx_site_tmplvar_contentvalues.value = '21') OR (modx_site_tmplvar_contentvalues.value = '12') OR (modx_site_tmplvar_contentvalues.value = '13') OR (modx_site_tmplvar_contentvalues.value = '4') OR (modx_site_tmplvar_contentvalues.value = '26') OR (modx_site_tmplvar_contentvalues.value = '1а') OR (modx_site_tmplvar_contentvalues.value = '16(а)') OR (modx_site_tmplvar_contentvalues.value = '14') OR (modx_site_tmplvar_contentvalues.value = '79')))
Замечание: можно условие записать и через IN — на скорость и описываемое поведение запроса это никак не повлияет.
При явном указании индекса [idx_value_tv] (это самый быстрый индекс) запрос выполняется 0.0105 сек. Если никакой индекс явно не указывать, то запрос выполняется 0.0161 сек. Здесь MySQL самостоятельно выбирает индекс [idx_value_tv] 0.0056 сек.
Пример 2
SELECT DISTINCT `modx_site_tmplvar_contentvalues`.contentid AS id FROM `modx_site_tmplvar_contentvalues` WHERE ((`modx_site_tmplvar_contentvalues`.tmplvarid IN (195, 207)) AND (`modx_site_tmplvar_contentvalues`.value IN ('13369','13605','18477','17217','15807','12812','13615','19166','12440','12993','13733','15011','13362','12693','13357','19499','13606')))
При явном указании индекса [idx_value] (это самый быстрый индекс для данного запроса) запрос выполняется 0.0059 сек. Если никакой индекс явно не указывать, то запрос выполняется 0.0182 сек. Здесь MySQL самостоятельно выбирает индекс [idx_value] 0.0123 сек.
Пример 3 (с использованием таблицы ресурсов)
SELECT modResource.id AS bigId, modResource.properties AS properties FROM `modx_site_content` AS `modResource` LEFT JOIN `modx_site_tmplvar_contentvalues` `RRR` ON ( RRR.contentid = modResource.id AND RRR.tmplvarid = 30 ) WHERE ( ( `modResource`.`template` = 25 OR `RRR`.`value` = '12' ) AND `modResource`.`class_key` = 'modDocument' AND `modResource`.`deleted` = 0 AND `modResource`.`published` = 1 AND `modResource`.`id` IN (2223,2230,2234,2244,2250,2256,2258,2259,2261,2266,2267,2277,2299,2300,2314,2315,2316,2322,2346,2347,2348,2349,2350,2354,2355,2356,2357,2358,2365,2368,2373,2379,2384,2385,2386,2387,2391,2394,2401,2415,2425,2427,2436,2441,2442,2444,2445,2451,2455,2472,2483,2487,2506,2568,2571,2572,2577,2580,2581,2584,2591,2593,2606,2607,2619,2626,2628,2632,2637,2647,2674,2675,2676,2677,2678,2682,2686,2699,2704,2706,2712,2734,2764,2784,2796,2797,2800,2806,2807,2808,2809,2817,2819,2823,2832,2839,2849,2850,2860,2929,2931,2934,2940,3243,3248,3351,3352,3359,3365,3367,3392,3398,3399,3403,3405,3413,3419,3420,3424,3435,3441,3447,3452,3453,3454,3460,3469,3470,3477,3478,3482,3489,3502,3506,3511,3512,3515,3527,3536,3543,3552,3562,3566,3573,3575,3586,3596,3604,3606,3609,3615,3656,3666,3675,3677,3678,3681,3687,3691,3695,3707,3708,3711,3720,3725,3729,3733,3734,3742,3758,3767,3774,3775,3782,3783,3784,3785,3787,3790,3792,3793,3795,3799,3802,3803,3805,3807,3808,3818,3822,3831,3833,3839,3852,3859,3881,3892,3899,3906,3907,3912,3916,3917,3927,3929,3934,3936,3938,3947,3954,3958,3961,3962,3966,3967,3970,3971,3974,3984,3987,3990,4006,4008,4009,4018,4019,4021,4025,4030,4031,4044,4045,4069,4073,4078,4088,4095,4103,4104,4106,4107,4133,4134,4137,4138,4141,4145,4169,4173,4174,4194,4216,4241,4242,4243,4835,5598,5599,5621,5622,5623,5626,5627,5628,5629,5653,5681,5682,6613,6615,7487,7625,7627,7628,7629,7659,7660,7671,7679,7777,7782,7785,7786,7792,7793,7814,7841,11106,11186,11202,21078,21096,21097,21100,21101,21102,21104,21105,21113,21163,21165,21166,21170,21172,21173,21174,21175,21178,21180,21182,21190,21191,21193,21201,21215,21222,21226,21232,21256,21257,21261,21262,21263,21264,21265,21266,21267,21268,21269,21280,21323,21324,21325,21332,21337,21338,21341,21343,21378,21380,21381,21382,21383,21385,21392,21393,21414,21427,21478,21479,21523,21524,21530,21531,21534,21535,21574,21575,21576,21580,21582,21583,21601,21602,21618,21624,21639,21640,21641,21645,21646,21647,21649,21650,21651,21654,21663,21665,21699,21700,21704,21706,21708,21710,21711,21712,21724,21748,21787,21788,21794,21811,21815,21816,21819,21820,21821,21823,21837,21838,21863,21872,21891,21905,21988,21991,22004,22315,22664,22668,22669,22671,22672,22673,22674,22677,22678,22679,22680,22683,22685,22758,22767,22768,22769,22775,22776,22814,22816,22827,22829,22833,22976,22980,22981,22983,22984,22985,22994,23606,23645,23647,23744,23745,23746,23748,23750,23752,23756,23759,23761,23768,23776,24266,24581,24582,24584,24585) )
Этот запрос выполняется 0.0072 сек. При этом MySQL самостоятельно выбирает индекс (PRIMARY).
Если этот индекс указать явно, то запрос выполняется за 0.0066. Т.е. из стандартного набора индексов (содержащихся в таблице ресурсов) в этом запросе MySQL выбирает индекс (PRIMARY) всего 0.0006 сек.
Далее в таблицу ресурсов добавляем индекс
Добавляем в таблицу ресурсов индекс:
ALTER TABLE `modx_site_content` ADD INDEX `idx_id_class_published_deleted` ( `id`, `class_key`, `published`, `deleted` ))
Снова выполняем запрос — 0.0345 сек. При этом MySQL по-прежнему выбирает индекс (PRIMARY), но выбирает уже 0.0273 сек.
Далее в таблицу ресурсов добавляем 2-й индекс:
ALTER TABLE `modx_site_content` ADD INDEX `idx_class_published_deleted_id` (`class_key`, `published`, `deleted`, `id`))
Выполняем запрос — 0.23 сек. При этом MySQL по-прежнему выбирает индекс (PRIMARY), но выбирает аж 0.22 сек. Ужасный результат.
Замечание. Если в 3-м примере явно указать один из добавленных индексов, то запрос выполняется существенно медленнее, чем изначально, когда MySQL самостоятельно выбирает индекс (PRIMARY). Т.е. эти индексы для данного запроса неподходящие (почему — не совсем понятно).
— Таким образом, удалил все собственные индексы из таблицы ресурсов. От греха подальше.
Что делать с собственными индексами в таблице (modx_site_tmplvar_contentvalues) — вопрос открытый. Опасная эта вещь — добавлять индексы.