приветствую! 
впал в творческий ступор, требуется имерженси хелп)

таблица (Т1)  вида:
|-- ID --|-- TITLE --|

таблица Т2 вида:
|-- ID --|-- ITEM_ID --|-- OPTION_ID --|-- VALUE --|

в таблице Т1 хранятся названия элементов, Т2 - содержит набор данных для элементов Т1.

нужно выбрать все записи из Т1, которые удовлетворяют условию запроса.  
запрос  формируется на основе массива связок вида  T2.option_id=$val  

пока ничего в голову, кроме цикличного JOIN-а всех опций не приходит.. 
например у нас есть массив условий:


то запрос для него формируется следующего вида:


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

или может имеет смысл делать единую таблицу Т1 т Т2 со всеми опциями?  ( описывал тут) 

ИМХО JOIN'ы лучше не использовать, лучше через where

PS Какая-то у Вас извращенная задача стоит.

ваш запрос выведет все записи, для которых совпала ХОТЬ одна опция... 


а что в ней извращенного? 
обычный поиск  по базе данных =)

Добавьте к нему

AndreyIQ,  
таблица Т2 содержит данные такого вида:

|-- ID --|-- ITEM_ID --|---- OPTION_ID ----|----- VALUE --|
|-- 1 ---|------- 1 ------|--- (1)материал ---|--- металл --|
|-- 2 ---|------- 1 ------|--- (1)материал ---|--- карбон --|
|-- 3 ---|------- 1 ------|--- (2)давление ---|----- 2.5 -----|
|-- 4 ---|------- 1 ------|----(3)сечение ----|------- 50-----|
|-- 5 ---|------- 2 ------|--- (1)материал ---|-- пластик --|
|-- 6 ---|------- 2 ------|--- (2)давление ---|----- 2.5 -----|

а поисковый запрос:

материал='металл,алюминий',
давление=2.5

соответственно выбрать нужно все ITEM_ID  для которых выполняется условие:
материал=(метал или аллюминий) и давление=(2.5)  (в этом примере получается только ITEM_ID=1 т.к. у ITEM_ID=2 материал не удовлетворяет условию: 'металл или алюминий',

Добавлено @ 17:52

мои весьма поверхностные  знания  mysql почему-то говорят мне что такая операция - выполняется  как бы "вторым кругом" т.е. сначала выполняется выборка без этого условия, и уже потом выполняется выборка еще раз в полученном результате.

это я к тому а не устанет ли mysql перебирать такие объемы данных  (около миллиона записей в таблице Т1 и на каждую из них приходится в среднем 10 записейв в Т2 - тоесть 10 миллионов.  


Это сообщение отредактировал(а) z-END - 20.9.2011, 17:52

А вот это уже не твоя забота... если ему станет плохо - он скажет.

как раз тики моя))) если он будет это медленно выполнять все придется переделывать...

z-END, хватит ваньку-то валять. Вот КОГДА будет выполняться медленно - ТОГДА и будем думать над увеличением производительности. Но если ты заранее изучишь план выполнения и построишь необходимые для оптимизации индексы (с учётом наполнения и селективности) - это ТОГДА так и не наступит.

Akina, ответ достойный советского автопрома)))) 
давайте сделаем ладу-приору, а там уж если будет кривая, будем думать))))

z-END, если ты изначально намерен выпускать лады-приоры - может, задуматься о перепрофилировании завода вообще? или закрыть его к чёртовой матери?

У Вас большое кол-во OPTION_ID?

AndreyIQ, 
в базе будет хранится порядка 100 видов, для каждого вида есть свой набор опций - в среднем порядка 10 шт.  получается что option_id - будет около 1000.



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

Оптимальный вариант - это правильное построение схемы данных, структур таблиц и индексов. 10кк записей - не так уж и много. А если селективность запроса высока - так и вовсе ерунда. Лишь бы не нарываться на прямой просмотр или там файловое кэширование. Но на поставленной задаче и описанной структуре как раз всего этого быть не должно, и запрос должен работать на уровне десятков мс.

приветствую вас Капитан Очевидность)
именно этот вопрос я и пытаюсь понять:

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

применительно к текущей ситуации - добавил 1.5 млн строк в Т2  и около 60 тыс в Т1.  

цифры меня шокировали... 
мой вариант:


вариант с where на 4 сек. медленней


вот структура таблиц:
Т1


Т2


Профилирование дает приблизительно такой результат: 

Хотелось бы еще план увидеть

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, спасибо! как в норму приду буду изучать изложенное..


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

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


Тогда не слушай умных, спрашивай глупых, но опытных.  


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