Объединение больших таблиц - как ускорить запрос?

[supermike]

Задача
Есть 3 таблицы(Modx) в 2х из них по 90+ тысяч строк, не суть важно.
Проблема в том, что в SELECT'е нужно объединить эти таблицы и вывести отфильтрованные по нескольким условиям (BMW, красная, стоит от 200к) данные.
Должно получиться:
2014-11-28-4.png

Структура таблиц
Таблица 1 `modx_site_content` содержит объекты поиска, допустим, 90к+ записей.
В ней столбцы:
id,
pagetitle(название объекта - уникальный идентификатор)

Таблица 2 `modx_site_tmplvar_contentvalues` содержит свойства объектов из 1й таблицы.
В ней столбцы:
id,
value(значение свойства: красная, BMW, 200'000, ...),
tmplvarid(тип свойства: цвет, марка, стоимость. Здесь указывается ссылка на таблицу 3),
contentid(ссылка на объект(таблица 1), которому принадлежит свойство)

Таблица 3 `modx_site_tmplvars` содержит типы свойств.
В ней столбцы:
id,
name(тип свойств: цвет, ...)

Проблема
У меня получилось сделать только следующим образом: объединить таблицы, выбрать все данные и только потом среди них найти нужные мне. Это занимает 5 секунд(долго) да и очень некрасиво. Какую оптимизацию можно провести, можно ли вытягивать только нужные данные, без предварительной загрузки всей таблицы? Пытался сделать данный запрос с помощью INNER JOIN, не получилось.
Что если вытягивать объекты через свойства? НоДобавив в content новый столбец

Мой запрос:

SELECT * FROM ( SELECT

`content`.`pagetitle`,
`content`.`id`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 5 ) as `color`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 4 ) as `brand`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 7 ) as `complectation`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 6 ) as `inches`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 9 ) as `price`,
(SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` = `content`.`id` AND `tmplvarid` = 8) as `transmition`

FROM `modx_site_content` as `content` WHERE `parent` = '. $contentid .') as z
WHERE 1 = 1

AND z.color = "WHITE"
AND z.brand = "AUDI"
AND z.transmition = "AUTO"
AND z.inches = "17"
AND z.price <= "300013"

LIMIT 100

Хотел попробовать вытянуть через сами свойства нужные мне объекты, но не могу понять как вытянуть несколько свойств:
SELECT `value` FROM `modx_site_tmplvar_contentvalues` WHERE `contentid` IN(SELECT `id` FROM `modx_site_content` WHERE `parent` = 1) AND `tmplvarid` = 5 AND `value` = "GRAY";

Системные данные
"Профилирование" в MySQL говорит, что 70% времени ушло на отправку данных клиенту. Хотя отправляется всего 30 найденных строк. Или вся таблица?

Буду благодарен любым подсказкам, размышлениям.

2014-11-28-3.png 2014-11-28-2.png 2014-11-28-1.png

[Vapaamies]

Кучу вложенных select-ов нужно заменить простым объединением, а поля выбирать с case when tmplvarid = <значение>.

[Аватар]

Так можно пробовать, не факт что быстрей будет - обилие LEFT JOIN

SELECT `content`.`pagetitle`,`content`.`id`,
    M1.`value` AS `color`,
    M2.`value` AS `inches`
  FROM `modx_site_content` as `content`
    LEFT JOIN `modx_site_tmplvar_contentvalues` AS M1 ON M1.`contentid` = `content`.`id` AND M1.`tmplvarid` = 5
    LEFT JOIN `modx_site_tmplvar_contentvalues` AS M2 ON M2.`contentid` = `content`.`id` AND M2.`tmplvarid` = 6
  WHERE M1.color = "WHITE" AND
        M2.`value` = "17"

Кучу вложенных select-ов нужно заменить простым объединением

Не понял как это

[supermike]

Сообщение от Аватар

Так можно пробовать, не факт что быстрей будет - обилие LEFT JOIN

SELECT `content`<...>

Вау! Огромное спасибо! Работает чуть меньше секунды, буду проверять задержку на реальном проекте где под миллион записей. В любом случае, на сколько я понимаю, дальше, чтобы существенно повысить скорость, можно только оптимизировать базу - скажем, вынести таблицу в оперативку?

Буду сейчас искать инфу по совету Vapaamies.

[Аватар]

В таблице modx_site_tmplvar_contentvalues актуально наличие индекса по contentid,tmplvarid

[supermike]

Индексацию, конечно же, добавил, его небыло только в столбце VALUE, но добавление не повлияло на производительность.
P.S. проверьте личку.

[supermike]

Сообщение от Vapaamies

Кучу вложенных select-ов нужно заменить простым объединением, а поля выбирать с case when tmplvarid = <значение>.

А можно пример? Не могу уловить идею

[Vapaamies]

Вчера уже спал на ходу, до конца не докумекал. Способ известный, но требует группировки (схлопывания) значений. В современных СУБД то же самое реализуется ключевым словом pivot.

select
  c.id, c.pagetitle,
  min(case when v.tmplvarid = 5 then v.value end) color,
  min(case when v.tmplvarid = 6 then v.value end) inches
from
  modx_site_content c,
  modx_site_tmplvar_contentvalues v
where
  v.content_id = c.id
group by
  c.id, c.pagetitle;

[supermike]

Сообщение от Vapaamies

Вчера уже спал на ходу, до конца не докумекал. Способ известный, но требует группировки (схлопывания) значений. В современных СУБД то же самое реализуется ключевым словом pivot.

Спасибо за ответ, открыл для себя pivot, это вещь... На сколько я понимаю, в этом запросе нельзя отфильтровать данные по цвету, к примеру? Ведь таблицы мы создаем на лету и они не существуют.

[Vapaamies]

Сообщение от supermike

На сколько я понимаю, в этом запросе нельзя отфильтровать данные по цвету, к примеру?

А что мешает обернуть запрос в еще один, и во внешнем сделать нужный отбор?