Mysql - Evil Query

[Иван Шумов]

Для одного заказчика занялся созданием фильтра (по работе) ... что из этого получилось:

	SELECT DISTINCT

	  c.*,

	 (SELECT GROUP_CONCAT(ss_c_params_acceptance.value SEPARATOR '<br/>') FROM ss_catalog_acceptance LEFT JOIN ss_c_params_acceptance ON ss_catalog_acceptance.ac_id = ss_c_params_acceptance.id WHERE ss_catalog_acceptance.item = c.id ) as acceptance_1,

	 (SELECT GROUP_CONCAT(ss_c_params_voltage.value SEPARATOR '<br/>') FROM ss_catalog_voltage LEFT JOIN ss_c_params_voltage ON ss_catalog_voltage.vt_id = ss_c_params_voltage.id WHERE ss_catalog_voltage.item = c.id) as voltage_1,

	 (SELECT GROUP_CONCAT(ss_c_params_out_signal.value SEPARATOR '<br/>') FROM ss_catalog_out_signal LEFT JOIN ss_c_params_out_signal ON ss_catalog_out_signal.os_id = ss_c_params_out_signal.id WHERE ss_catalog_out_signal.item = c.id) as out_signal_1,

	 (SELECT GROUP_CONCAT(CONCAT_WS('x',ss_c_params_body_type.value,ss_catalog_body_type.height) SEPARATOR '<br/>') FROM ss_catalog_body_type LEFT JOIN ss_c_params_body_type ON ss_catalog_body_type.bt_id = ss_c_params_body_type.id WHERE ss_catalog_body_type.item = c.id) as body_type_1

	 FROM ss_catalog_rus as c

	 WHERE

	 `group` = 'ocxo' AND

	 c.id = ANY (
  SELECT DISTINCT c2.id

	 FROM ss_catalog_rus as c2,ss_catalog_voltage, ss_catalog_acceptance, ss_catalog_body_type

	 WHERE

	 (ss_catalog_voltage.vt_id = 1 OR ss_catalog_voltage.vt_id = 2 OR ss_catalog_voltage.vt_id = 3)

	 AND ss_catalog_voltage.item = c2.id

	 AND (ss_catalog_acceptance.ac_id = 1 OR ss_catalog_acceptance.ac_id = 2 OR ss_catalog_acceptance.ac_id = 3)

	 AND ss_catalog_acceptance.item = c2.id

	 AND (ss_catalog_body_type.bt_id = 1 OR ss_catalog_body_type.bt_id = 2 OR ss_catalog_body_type.bt_id = 3 OR ss_catalog_body_type.bt_id = 4 OR ss_catalog_body_type.bt_id = 5 OR ss_catalog_body_type.bt_id = 6)

	 AND ss_catalog_body_type.item = c2.id

	 AND ss_catalog_body_type.height < '16'

	 AND c2.type = '1'

	 AND c2.temperature_unstable*POWER(10, (9 - c2.temperature_unstable_degree))

	 AND c2.permanent_unstable*POWER(10, (9 - c2.permanent_unstable_degree))

	 )

только не сходите с ума

для желающих осилить - бэкап базы приложен

посмотреть великий смысл запроса можно тут

ЗЫ: а все ради сборки всех данных из 9и таблиц в один 2хмерный массив на шаблонизатор

ЗЫЫ: конечно понятно что половина WHERE генерится из PHP, но до этого все было протестировано "руками" так сказать

morion.zip

[Dimitry Wolotko]

Маниак - но это нормально ©

[Tokolist]

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

  select
	pa.value,
	pv.value,
	po.value,
	concat_ws('x', pb.value, cb.height)
  from ss_catalog_rus cr

  join ss_catalog_voltage cv on cv.vt_id in(1, 2, 3) and cv.item = c2.id
  join ss_catalog_acceptance ca on ca.ac_id in(1, 2, 3) and ca.item = c2.id
  join ss_catalog_body_type cb on cb.bt_id in(1, 2, 3, 4, 5, 6) and cb.item = c2.id and cb.height < '16'

  join ss_c_params_acceptance pa on ca.ac_id = pa.id and ca.item = cr.id
  join ss_c_params_voltage pv on cv.vt_id = pv.id and cv.item = cr.id

  join ss_catalog_out_signal co on co.item = cr.id
  join ss_c_params_out_signal po on po.id = co.os_id

  join ss_c_params_body_type pb on cn.bt_id = pb.id and bt.item = cr.id
  where
	cr.group = 'ocxo' and
	cr.type = '1' and
	cr.temperature_unstable * power(10, (9 - cr.temperature_unstable_degree)) and
	cr.permanent_unstable * power(10, (9 - cr.permanent_unstable_degree))

Здесь еще вместо in(1, 2, 3, 4, 5, 6) можно использовать between (зависит от целей)

Edited August 22, 2008 by Tokolist

[Иван Шумов]

отредактировал топик для любопытных

[Tokolist]

Не правильно сначала понял сути запроса. Теперь все прояснилось. Первый вариант был такой:

select distinct
  c.model,
  concat(c.frequency_min, '..', c.frequency_max) frequency,
  concat('±', c.temperature_unstable, 'х10<sup>-9</sup>') temperature_unstable,
  concat('±', c.temperature_unstable_degree, 'х10<sup>-8</sup>/год') temperature_unstable_degree,
  (select group_concat(cpa.value separator '<br/>') from ss_catalog_acceptance ca, ss_c_params_acceptance cpa where ca.ac_id = cpa.id and ca.item = c.id) acceptance,
  (select group_concat(cpv.value separator '<br/>') from ss_catalog_voltage cv, ss_c_params_voltage cpv where cv.vt_id = cpv.id and cv.item = c.id) voltage,
  (select group_concat(cpos.value separator '<br/>') from ss_catalog_out_signal cos, ss_c_params_out_signal cpos where cos.os_id = cpos.id and cos.item = c.id) out_signal,
  (select group_concat(concat_ws('x', cpbt.value, cbt.height) separator '<br/>') from ss_catalog_body_type cbt, ss_c_params_body_type cpbt where cbt.bt_id = cpbt.id and cbt.item = c.id) body_type
from
  ss_catalog_rus c,
  ss_catalog_voltage cv,
  ss_catalog_body_type cbt,
  ss_catalog_acceptance ca
where
  ca.item = c.id and
  ca.ac_id in (1, 2, 3) and

  cbt.item = c.id and
  cbt.bt_id in (1, 2, 3, 4, 5, 6) and
  cbt.height < 16 and

  cv.item = c.id and
  cv.vt_id in (1, 2, 3) and

  c.group = 'ocxo' and
  c.type = 1 and
  c.temperature_unstable * power(10, (9 - c.temperature_unstable_degree)) and
  c.permanent_unstable * power(10, (9 - c.permanent_unstable_degree))

Но он в два раза медленней второго:

select distinct
  c.model,
  concat(c.frequency_min, '..', c.frequency_max) frequency,
  concat('±', c.temperature_unstable, 'х10<sup>-9</sup>') temperature_unstable,
  concat('±', c.temperature_unstable_degree, 'х10<sup>-8</sup>/год') temperature_unstable_degree,
  (select group_concat(cpa.value separator '<br/>') from ss_catalog_acceptance ca, ss_c_params_acceptance cpa where ca.ac_id = cpa.id and ca.item = c.id) acceptance,
  (select group_concat(cpv.value separator '<br/>') from ss_catalog_voltage cv, ss_c_params_voltage cpv where cv.vt_id = cpv.id and cv.item = c.id) voltage,
  (select group_concat(cpos.value separator '<br/>') from ss_catalog_out_signal cos, ss_c_params_out_signal cpos where cos.os_id = cpos.id and cos.item = c.id) out_signal,
  (select group_concat(concat_ws('x', cpbt.value, cbt.height) separator '<br/>') from ss_catalog_body_type cbt, ss_c_params_body_type cpbt where cbt.bt_id = cpbt.id and cbt.item = c.id) body_type
from
  ss_catalog_rus c
where
  c.id in (
	select
	  c.id
	from
	  ss_catalog_rus c,
		ss_catalog_acceptance ca,
		ss_catalog_body_type cbt,
		ss_catalog_voltage cv
	  where
	  c.group = 'ocxo' and
	  c.type = 1 and
	  c.temperature_unstable * power(10, (9 - c.temperature_unstable_degree)) and
	  c.permanent_unstable * power(10, (9 - c.permanent_unstable_degree)) and

	  ca.item = c.id and
	  ca.ac_id in (1, 2, 3) and

	  cbt.item = c.id and
	  cbt.bt_id in (1, 2, 3, 4, 5, 6) and
	  cbt.height < 16 and

	  cv.item = c.id and
	  cv.vt_id in (1, 2, 3)
  )

Который, в принцыпе, почти идентичен твоему.

Оптимизация sql-запросов всегда была моим слабым местом.

[Иван Шумов]

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

[Tokolist]

Просто, теоретически подзапросы всегда жрут больше ресурсов, но надо смотреть EXPLAIN или PLAN или подобные (зависит от базы). Плюс в базе не выставлены индексы. Короче, запрос надо проверять в реальных условиях. Но я, как минимум, улучшил (имхо) читаемость кода, что тоже (опять имхо) немаловажно.

А это у тебя изначальный вариант или результат оптимизации?

[Иван Шумов]

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

на счет подзапросов ... учитывая цель запроса - они здесь на своем месте, к тому же индексы их не ускорят в общем-то - таблицы параметров не большие - по 2-10 строк, сильно жрать в данном случае должна ф-ция GROUP_CONCAT обрабатывающая CONCAT_WS .... там вообще жесть - но думаю понимаешь что сделано во имя простоты работы с шаблонизатором. Зато весь php обработчик занимает десяток строк, так что допустимая жертва

[Tokolist]

на счет подзапросов ... учитывая цель запроса - они здесь на своем месте

Я имел ввиду подзапрос в IN. Я так подозреваю, что его результат кэшируется и он не выполняется при каждом проходе. Теоретически так и должно быть. Вроде в этом и причина большей скорости. Т.е. получается обработка меньшего количества строк, чем при объединении 4-х таблиц.

к тому же индексы их не ускорят в общем-то - таблицы параметров не большие - по 2-10 строк

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

сильно жрать в данном случае должна ф-ция GROUP_CONCAT обрабатывающая CONCAT_WS ....

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

там вообще жесть - но думаю понимаешь что сделано во имя простоты работы с шаблонизатором. Зато весь php обработчик занимает десяток строк, так что допустимая жертва

Как раз все нормально сделано. Меньше обращений к базе, скорость также должна быть большей (имхо), плюс все логично правильно.

[Иван Шумов]

Черт, даже добавить нечего

[Tokolist]