Форум программистов
 
Регистрация на форуме тут, о проблемах пишите сюда - alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail, а тут можно восстановить пароль

Купить рекламу на форуме 15-35 тыс рублей в месяц

Вернуться   Форум программистов > Web программирование > SQL, базы данных
Регистрация

Восстановить пароль
Повторная активизация e-mail

Ответ
 
Опции темы Поиск в этой теме
Старый 28.11.2014, 03:27   #1
supermike
Пользователь
 
Регистрация: 13.09.2013
Сообщений: 13
По умолчанию Объединение больших таблиц - как ускорить запрос?

Задача
Есть 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

Последний раз редактировалось supermike; 28.11.2014 в 03:33.
supermike вне форума Ответить с цитированием
Старый 28.11.2014, 03:59   #2
Vapaamies
Ваш К. О.
Участник клуба
 
Аватар для Vapaamies
 
Регистрация: 26.12.2012
Сообщений: 1,675
По умолчанию

Кучу вложенных select-ов нужно заменить простым объединением, а поля выбирать с case when tmplvarid = <значение>.
Vapaamies вне форума Ответить с цитированием
Старый 28.11.2014, 10:23   #3
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,050
По умолчанию

Так можно пробовать, не факт что быстрей будет - обилие 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-ов нужно заменить простым объединением
Не понял как это
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию
Аватар вне форума Ответить с цитированием
Старый 28.11.2014, 11:55   #4
supermike
Пользователь
 
Регистрация: 13.09.2013
Сообщений: 13
По умолчанию

Цитата:
Сообщение от Аватар Посмотреть сообщение
Так можно пробовать, не факт что быстрей будет - обилие LEFT JOIN
Код:
SELECT `content`<...>
Вау! Огромное спасибо! Работает чуть меньше секунды, буду проверять задержку на реальном проекте где под миллион записей. В любом случае, на сколько я понимаю, дальше, чтобы существенно повысить скорость, можно только оптимизировать базу - скажем, вынести таблицу в оперативку?

Буду сейчас искать инфу по совету Vapaamies.
supermike вне форума Ответить с цитированием
Старый 28.11.2014, 12:00   #5
Аватар
Старожил
 
Аватар для Аватар
 
Регистрация: 17.11.2010
Сообщений: 19,050
По умолчанию

В таблице modx_site_tmplvar_contentvalues актуально наличие индекса по contentid,tmplvarid
Если бы архитекторы строили здания так, как программисты пишут программы, то первый залетевший дятел разрушил бы цивилизацию
Аватар вне форума Ответить с цитированием
Старый 28.11.2014, 12:11   #6
supermike
Пользователь
 
Регистрация: 13.09.2013
Сообщений: 13
По умолчанию

Индексацию, конечно же, добавил, его небыло только в столбце VALUE, но добавление не повлияло на производительность.
P.S. проверьте личку.
supermike вне форума Ответить с цитированием
Старый 28.11.2014, 14:57   #7
supermike
Пользователь
 
Регистрация: 13.09.2013
Сообщений: 13
По умолчанию

Цитата:
Сообщение от Vapaamies Посмотреть сообщение
Кучу вложенных select-ов нужно заменить простым объединением, а поля выбирать с case when tmplvarid = <значение>.
А можно пример? Не могу уловить идею
supermike вне форума Ответить с цитированием
Старый 28.11.2014, 19:59   #8
Vapaamies
Ваш К. О.
Участник клуба
 
Аватар для Vapaamies
 
Регистрация: 26.12.2012
Сообщений: 1,675
По умолчанию

Вчера уже спал на ходу, до конца не докумекал. Способ известный, но требует группировки (схлопывания) значений. В современных СУБД то же самое реализуется ключевым словом 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;
Vapaamies вне форума Ответить с цитированием
Старый 30.11.2014, 11:20   #9
supermike
Пользователь
 
Регистрация: 13.09.2013
Сообщений: 13
По умолчанию

Цитата:
Сообщение от Vapaamies Посмотреть сообщение
Вчера уже спал на ходу, до конца не докумекал. Способ известный, но требует группировки (схлопывания) значений. В современных СУБД то же самое реализуется ключевым словом pivot.
Спасибо за ответ, открыл для себя pivot, это вещь... На сколько я понимаю, в этом запросе нельзя отфильтровать данные по цвету, к примеру? Ведь таблицы мы создаем на лету и они не существуют.
supermike вне форума Ответить с цитированием
Старый 30.11.2014, 11:39   #10
Vapaamies
Ваш К. О.
Участник клуба
 
Аватар для Vapaamies
 
Регистрация: 26.12.2012
Сообщений: 1,675
По умолчанию

Цитата:
Сообщение от supermike Посмотреть сообщение
На сколько я понимаю, в этом запросе нельзя отфильтровать данные по цвету, к примеру?
А что мешает обернуть запрос в еще один, и во внешнем сделать нужный отбор?
Vapaamies вне форума Ответить с цитированием
Ответ
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Как сделать запрос к MySQL не зная префикс таблиц? tim21701 БД в Delphi 6 29.01.2012 07:44
Направьте в нужное русло. Сравнение двух больших таблиц Excel hijke Microsoft Office Excel 6 05.05.2011 13:24
Объединение 2-х таблиц newStudent SQL, базы данных 2 21.04.2011 19:37
Как правилно составить запрос из несколких таблиц или запросов Rebbit Microsoft Office Access 1 25.07.2010 18:49
Как оптимизировать запрос MySQL с выборкой из двух таблиц. Johnatan SQL, базы данных 6 13.04.2008 03:10