Вопрос по грамотной организации фильтров поиска. Например обычная форма поиска с кучей полей как на сайте знакомств или интернет магазине, когда можно фильтровать по куче параметров. Но как заставить MYSQL использовать индексы для этого? Ведь если будет поиск без индексов при большом размере таблицы будет очень долго все искаться... я как понял что OR почти всегда лучше заменять UNION Но какие еще могут быть вариант? Сделать один большой составной индекс с кучей полей и после чего по максимум все поля отсортировать через него через where and and and ..... Ведь вроде в таком случае индексы будут работать если сохранен точный порядок (как поля обозначены в индексе, в том порядке и писать условие and)? Работают ли индексы для сортировки? В таком случае поле по которому будет идти сортировка должно быть последним? Но конечно больше всего интересно как реализовать комбинацию OR и AND Ведь на сколько я понял вызов OR либо IN сразу убивает работу индексов.
Даже если индексы есть, они не обязательно будут применяться: планировщик на то и существует, что бы собирать статистику и делать план выполнения запроса. А все эти "оптимизации" не более эффективны, чем двойные / одинарные кавычки в пыхе - гомеопатия и самовнушение ), предварительно же натыканные индексы (кроме очевидных) так вообще зло. На самом деле алгоритм прост: 1. Включаете slow log, выставляете тайминг, собираете статистику 2. Определяете важные запросы по параметру количество * время, кастуете на запрос EXPLAIN и внимательно смотрите как планировщик раскладывает его на составные части. 3. Добавляете индекс в те части и на те поля, что по параметру количество * время выше остальных. 4. Проверяете, что индекс используется и время запроса изменилось в лучшую сторону 5. Уменьшаете тайминг, ждете нового лога, возвращаетесь в п.1 6. Не забываем чистить кэш ) Поясню: то что в запросе используется N полей, совсем не означает, что нужно проставлять индексы на все, тем более не стоит делать составной индекс размером с половину таблицы, потому как профит от него становится сомнительным, а стоимость будет высокой. Это при условии, что планировщик не проигнорирует вообще все ваши индексы, посчитав что без них будет быстрее. Пример: Код (Text): EXPLAIN ANALYSE SELECT * FROM items WHERE user_id = 123; Seq Scan on items (cost=0.00..58202.97 rows=58769 width=992) (actual time=0.007..84.467 rows=58372 loops=1) Filter: (user_id = 123) Rows Removed by Filter: 250710 Индексы идут лесом, потому как для получения результата один хер придется перебирать все блоки. Код (Text): EXPLAIN ANALYSE SELECT * FROM items WHERE user_id = 123 and category_id = 1; Bitmap Heap Scan on items (cost=1560.66..50918.45 rows=32023 width=992) (actual time=7.020..25.682 rows=26799 loops=1) Recheck Cond: ((user_id = 123) AND (category_id = 1)) Heap Blocks: exact=19100 -> Bitmap Index Scan on items_user_id_category_id_price_index (cost=0.00..1552.65 rows=32023 width=0) (actual time=4.424..4.424 rows=28402 loops=1) Index Cond: ((user_id = 123) AND (category_id = 1)) А тут таки появился индекс, потому что уже не все и bitmap позволит сделать меньше обращений к диску при этом не сожрав память под довольно большую (не по количеству строк, а данных в ней) таблицу. Код (Text): explain analyze select * from items where user_id=123 and category_id=1 limit 100; Limit (cost=0.00..184.17 rows=100 width=992) (actual time=0.008..0.235 rows=100 loops=1) -> Seq Scan on items (cost=0.00..58975.17 rows=32023 width=992) (actual time=0.007..0.233 rows=100 loops=1) Filter: ((user_id = 123) AND (category_id = 1)) Rows Removed by Filter: 792 И снова пропал индекс. Код (Text): explain analyze select * from items where user_id=123 and category_id=1 order by id limit 100; Limit (cost=0.42..723.00 rows=100 width=992) (actual time=4.639..4.811 rows=100 loops=1) -> Index Scan using items_pkey on items (cost=0.42..231390.91 rows=32023 width=992) (actual time=4.637..4.801 rows=100 loops=1) Filter: ((user_id = 123) AND (category_id = 1)) Rows Removed by Filter: 4293 Ну а теперь мы добавили сортировку по id, то индекс вновь появился, но таки другой. А почему так? Код (Text): explain (analyze, buffers) select * from items where user_id=123 and category_id=1 order by id limit 100; Limit (cost=0.42..723.00 rows=100 width=992) (actual time=4.639..4.811 rows=100 loops=1) -> Index Scan using items_pkey on items (cost=0.42..231390.91 rows=32023 width=992) (actual time=4.637..4.801 rows=100 loops=1) Filter: ((user_id = 123) AND (category_id = 1)) Rows Removed by Filter: 4293 Buffers: shared hit=4283 А потому что один хер все закешировалось и проще перебрать, чем лезть за индексами ) Такие дела. --- Добавлено --- субд пишут не идиоты, она в состоянии понять с какой стороны к запросу подойти, в каком порядке и с каким индексом )
кстати да иногда они не применяются не потому, что планировщик очень умный, а потому, что тупой. --- Добавлено --- ну xyй знает --- Добавлено --- вот это очень важный шаг, который многие просто пропускают. Нужно построить такой индекс, чтобы он юзался планировщиком. =)