z-END, а где в catalog_item_options индекс по item_id?
И с какой великомудрой целью там индекс (option_id, item_id)? уж лучше бы наоборот...

это через JOIN


Присоединённый файл ( Кол-во скачиваний: 6 )
 var1.PNG 15,00 Kb

это where

Добавлено через 7 минут и 12 секунд

не знал, что в составном индексе - порядок играет роль. 
поменял местами 

добавил

вариант с JOIN:  строки 0 - 29 (50,000 всего, запрос занял 13.0903 сек.)
вариант с WHERE: строки 0 - 29 (30 всего, запрос занял 29.9699 сек.)



Присоединённый файл ( Кол-во скачиваний: 5 )
 var2.PNG 12,27 Kb

Просто для интереса, что нибудь измениться если запрос написать, примерно так:

или так   

AndreyIQ,  первый вариант 



второй вариант.


сейчас заметил, что при добавлении данных в цикле по ошибке все даннные добавил два раза, по этому HAVING COUNT(item_id) получается не 2 а 4. это может быть проблемой?

Добавлено через 4 минуты и 50 секунд

это уже интересно, но все равно от заявленных: 


 очень далек... 

Если не ошибаюсь то от лимита Вы скорость не выиграете, т.к. используется группировка и сортирока, а для них необходимо сначала выбрать все данные.

LIMIT   phpmyadmin автоматом добавляет, но он касается только вывода данных. проверял без него - скорость та же... 

все таки продолжаю размышлять насчет создания отдельных  таблиц под каждый вид записей... 

z-END, давайте так - Вы не валите всё в кучу в хрен знает какой форме, а чётко даёте группу (show create table + explain select). И не из ПХПадмина, а с консоли сервера - нас ведь интересует происходящее именно на сервере, верно?

Akina,  да босс))) дам все что угодно=))

T1


T2


эксплейны каких запросов ?

Тех, скорость которых не устраивает, которые надо оптимизировать.

Добавлено @ 22:11
Да! explain не на пустых таблицах, пожалуйста, а на заполненных. Данными в количестве и соотношениях, приблизительно соответствующих реально-боевым.

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


1. база данных содержит данные о разных видах изделий, каждый вид изделий имеет свой набор опций.  
2. виды изделий  должны иметь возможность модифицироваться в процессе штатной эксплуатации. (т.е. могут добавляться или удалятся опции)

основная задача: организовать комбинированный поиск требуемых изделий с учетом специфических для каждого изделия опций. 


общие для всех видов опции:
- ID
- страна_производитель
- название
- тэги 
- цена
- производитель
- наличие 
- тип изделия
- популярность изделия
- дата обновления данных

виды расширенных опций:
тип (1): ДВИГАТЕЛЬ
1. тип (3-цилиндровый(1), 4-цилиндровый(2), 6 цилиндровый(3), 8-цилиндровый(4), роторный(5))
2. вес (int)
4. удельная мощность (int)
5. впрыск (карбюратор(1), инжектор(2))
6. топливо (бензин(1), дизель(2))
7. опции: (система старт/стоп(1); система отключения цилиндров(2);)

тип (2): КПП
8. тип (механика(1), автомат(2), вариатор(3), робот(4), dsg(5))
9. количество передач (int) 

тип (3): ТОРМОЗНАЯ СИСТЕМА
10. передние тормоза (дисковые(1), барабанные(2))
11. опционал передних тормозов (вентилируемые(1); перфорируемые(2))
12. задие тормоза (дисковые(1), барабанные(2))
13. опционал задних тормозов (вентилируемые(1); перфорируемые(2))
14. опционал тормозов (ABS(1); EBD(2); HillAssist(3),AutoHandbrake(4))

опции могут быть:
 - для каждого изделия подходит только один из возможных вариантов - впрыск двигателя(5) может быть ИЛИ карбюратор ИЛИ инжектор)
 - для каждого изделия может подходить как множество опций, так и ни одной - тормоза (14) могут быть И ABS И EBD И HA и AH. 


Поисковая задача должна реализвоать поиск следующего вида:

Необходимо найти все изделния из разделов Двигатели(1) и Тормозная система(3):
- от производителей АА или ББ
- находящиеся в наличии
- произведенные германии 
- нужны двигатели(1):
- - 6 цилиндровые (опция1=6)
- - инжектор (опция5=2)

- нужны тормозные системы(3):
- - передние тормоза дисковые (опция10=1)
- - задние торомза дисковые (опция12=1)
- - опционал тормозов ABS, EBD и HillAssist (опция14=1 + опция14=2 + опция14=3)

сейчас есть две таблицы catalog_items - которые хранят общие данные об изделиях  и таблица catalog_item_options в которых хранятся данные о опциях каждого изделия, допустим у нас есть тормозная система с:

10. передними тормозами дисковыми (1)
11. передние тормоза вентилируемые(1), перфорируемые(2)
12. задние барабанные (2)
14. абс(1) и HillAssist(3)

то в базу catalog_item_options данные заносятся
|--item_id--|--option_id--|--value--|
|---xxxx----|------10-----|---1-----| // дисковые
|---xxxx----|------11-----|---1-----| //вентидлируемые
|---xxxx----|------11-----|---2-----| //перфорируемые
|---xxxx----|------12-----|---2-----| //задний барабан
|---xxxx----|------14-----|---1-----| //ABS
|---xxxx----|------14-----|---3-----| //HillAsist

так вот, может уже на этом этапе не правильная логика процесса?

PS более того, изначально я рассматривал частный случай поиска по одному виду изделий, а требуется поиск различных (двигатель и тормоза в одном запросе)

На мой взглад вполне нормальная структура.

AndreyIQ, в общем случае да, однако учитывая специфику поиска, я вчера понял, что не могу в принципе составить запрос, который бы вообще выполнял такого рода выборку.. а это уже наводит меня не различные мысли... (хотя не могу сказать что хорошо разбираюсь в sql, но с бытовыми запросами проблем никогда не было)

Лимит в продуктивном решении предполагается?
Или пользователю все 50 тыщ покзать надо?


в точку!

Группировки тут  можно избежать используя семиджойны:


добавить индекс по catalog_item_options (item_id,option_id,value)

Пересмотреть порядок сортировки, чтобы не было desc, создать индекс на catalog_items(list_id,_visible,... поля сортировки). 

Если порядок сортировки надо выдержать именно этот - лепить костыли:
в catalog_items  добавить поля __ratio, __updated, _id. Заполнять из триггера значениями соотвественных полей умноженных на -1, включить их в индекс и сортироваться по ним asc

Форсить использование этих индексов.

Зачем, думаю, понятно. Чтобы избежать необходимости сортировки, заменить ее на сканирование индекса. Это возможно лишь когда у нас есть составной индекс, где сначала перечислены столбцы, по которым производится отбор, а затем поля сортировки в том порядке, в котором они перечислены в ордербай. Так же нужно следить, чтобы порядок сортировки не зависел от параметров сессии (языкове настройки, регистонезависмые сортировки, бинарные/лингвистические сортировки и т.п.), тогда индекс не сможет быть использован. Полагаю таймстамп - не тот случай.

Если нам удастся использовать индекс вместо ордербая, запрос будет мухой работать, когда без лимита из 60 тыщ будут выбираться 50 тыщ. Однако в случае, если без лимита под критерии будет попадать лишь одна запись, да в самом конце отсортированного списка... будут те же 20 сек


А вообще проблема типична. С ней в конце концов сталкиваются все любители гибких подходов. Реляционные базы любят четкость и определенность. Если атрибут используется логикой приложения, по нему производится отбор, он должен быть столбом таблицы, а не UDA


Это сообщение отредактировал(а) Zloxa - 28.9.2011, 09:31

привет осень... первый больничный в самом разгаре))) 


для пользователя будет организована постраничная навигация.
 

Zloxa, спасибо! как в норму приду буду изучать изложенное..


вот и я смотрел в эту сторону, но умные люди отговаривают...