Форум пользователей MySQL

ILYA · 13.07.2010 19:38:14

Доброе время суток, уважаемые!

На днях пришлось немного помучиться и «написать» вот такой страшный и ужасный запрос.

Прежде чем читать далее:
- это не «кричащая» задача – она уже сделана как есть и работает без сбоев
- это больше просьба дать совет как оптимизировать, найти новые подходы, о которых я не знал
- это, как по мне, что-то вроде головоломки.

Если не охото «ломать» голову – можно не читать далее, НО я все же хотел бы найти иной, более оптимальный и «красивый» способ решения.

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

Изначально задача стояла немного иная – и запрос был красивым и приятным, но к этому разделу постоянно изменялись требования и расширялись условия.
Этот фрагмент реализовывался под Друпал (со всеми вытекающими из этого подходами – типа основываемся на перехватах и не лезем в сторонний код, формируем вывод через систему theme, и таблицы тоже с «пейдженаторами» в отдельном своем модуле).

Так вот, ближе к «телу» - запрос (как в коде):

$sql="

SELECT n.title as `name`, 

        ua.dst as path, 

        n.nid as nid,

        ctph.field_physicians_state_value as state, 

        ctph.field_physicians_city_value as city, 

        IF(

            ISNULL(ctph.field_physicians_city_value) AND ISNULL(ctph.field_physicians_state_value),

            CONCAT(ctph.field_phys_off_city_value, ', ', ctph.field_phys_off_state_value),

            CONCAT(ctph.field_physicians_city_value, ', ', ctph.field_physicians_state_value)) as location,               # локация может быть или офиса или отдельного кабинете (речь идет о врачах), причем или та, или иная, а так как мы еще используем табличную темизацию на выходе, где это одна колонка – то мы и в запросе тоже должны иметь одну (а также это нужно для сортировки – тоже автоматической)

        ctph.field_physicians_sex_value as gender,

        ctph.field_physicians_phone1_value as phone,

        (SELECT GROUP_CONCAT(' ',td1.name)    # а здесь начинается ерунда (ниже привожу структуры таблицы) – нужно вывести обеденные значения  - в данном случае -  Degree

            FROM term_node as tn1, term_data as td1, node as n1 

            WHERE td1.vid=3 AND 

                tn1.nid = n.nid AND 

                tn1.tid = td1.tid AND 

                n1.nid=tn1.nid AND # эта часть не нужна – осталась исторически

                n1.vid=tn1.vid AND # так как в очередной раз изменилось ТЗ и появились ревизии (различные варианты для одного нода – один вариант публикуем, второй модерируем, третий в историю и так далее)

                n1.`status` <> 0) as degree,

        (SELECT GROUP_CONCAT(' ',td2.name)   # тоже самое, но уже по другому условию, но из той же таблицы.

            FROM term_node as tn2, term_data as td2, node as n2 

            WHERE td2.vid=4 AND 

                tn2.nid = n.nid AND 

                tn2.tid = td2.tid AND 

                n2.nid=tn2.nid AND  # аналогично

                n2.vid=tn2.vid AND  # речь о ревизии

                n2.`status` <> 0) as specialty,

        (SELECT GROUP_CONCAT(' ',td3.name)   # аналогично. Таблицы переименовываю, что бы не путаться 

            FROM term_node as tn3, term_data as td3, node as n3 

            WHERE td3.vid=5 AND 

                tn3.nid = n.nid AND 

                tn3.tid = td3.tid AND 

                n3.nid=tn3.nid AND # аналогично

                n3.vid=tn3.vid AND # речь о ревизии

                n3.`status` <> 0) as languages

FROM {content_type_physicians} as ctph 

RIGHT JOIN {node} as n ON n.nid = ctph.nid

LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid)

LEFT JOIN {term_node} as tn ON tn.nid = n.nid

LEFT JOIN {term_data} as td ON td.tid = tn.tid

WHERE $where AND

    $where_spec_subselect # опишу ниже – отдельный разговор

    $where_lang_subselect # аналогично

    td.vid in (3,4,5) AND  #  речь идет о словаре (здесь есть путаница, так как в таблице словарей ID называется vid и точно также в другой таблице – таблице ревизий ID тоже называется vid, поэтому отталкиваться следует от таблицы)

    n.language = '$language->language' AND # на сайте 2 языка

    n.vid = ctph.vid AND  # речь про ревижены

    n.`status` <> 0

GROUP BY n.nid

ORDER BY $order

”;

Таблицы закрыты в «{}» - это стандартная форма для друпала, что бы устанавливать префиксы таблицам, если они нужны (в данном случае не нужны – поэтому ленюсь и не везде указываю).

Структура используемых таблиц:
Основная – список всех нодов (любых типов)

CREATE TABLE node (

  nid int(10) unsigned NOT NULL auto_increment,

  vid int(10) unsigned NOT NULL default '0',

  `type` varchar(32) NOT NULL default '',

  `language` varchar(12) NOT NULL default '',

  title varchar(255) NOT NULL default '',

  uid int(11) NOT NULL default '0',

  `status` int(11) NOT NULL default '1',

  created int(11) NOT NULL default '0',

  `changed` int(11) NOT NULL default '0',

  `comment` int(11) NOT NULL default '0',

  promote int(11) NOT NULL default '0',

  moderate int(11) NOT NULL default '0',

  sticky int(11) NOT NULL default '0',

  tnid int(10) unsigned NOT NULL default '0',

  translate int(11) NOT NULL default '0',

  PRIMARY KEY  (nid),

  UNIQUE KEY vid (vid),

  KEY node_changed (`changed`),

  KEY node_created (created),

  KEY node_moderate (moderate),

  KEY node_promote_status (promote,`status`),

  KEY node_status_type (`status`,`type`,nid),

  KEY node_title_type (title,`type`(4)),

  KEY node_type (`type`(4)),

  KEY uid (uid),

  KEY tnid (tnid),

  KEY translate (translate)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/в этой таблице также определяется что ревизия, а что показываем – это определяет vid – все данные из других таблиц, где vid равен vid в этой таблице считать «настоящими»/

Структура дополнительных полей для отдельного нода (у нас это врачи):

CREATE TABLE content_type_physicians (

  vid int(10) unsigned NOT NULL default '0',

  nid int(10) unsigned NOT NULL default '0',

  field_physicians_street_value longtext,

  field_physicians_city_value longtext,

  field_physicians_state_value longtext,

  field_physicians_zip_value varchar(10) default NULL,

  field_physicians_phone1_value longtext,

  field_physicians_phone2_value longtext,

  field_physicians_hours_value longtext,

  field_physicians_sex_value longtext,

  field_physicians_picture_fid int(11) default NULL,

  field_physicians_picture_list tinyint(4) default NULL,

  field_physicians_picture_data text,

  field_physicians_id_value int(11) default NULL,

  field_phys_off_address_value longtext,

  field_phys_off_city_value longtext,

  field_phys_off_state_value longtext,

  field_phys_off_zip_value longtext,

  field_phys_off_phone1_value longtext,

  field_phys_off_phone2_value longtext,

  field_physicians_depart_value longtext,

  PRIMARY KEY  (vid),

  KEY nid (nid)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/связываются с ревизиями через vid, а с нодами – через nid, то есть vid здесь уникальный, а nid - нет/

Те самые ревижены (когда есть много вариантов одного и того же нода – тоесть ID один, но вариантов много):

CREATE TABLE node_revisions (

  nid int(10) unsigned NOT NULL default '0',

  vid int(10) unsigned NOT NULL auto_increment,

  uid int(11) NOT NULL default '0',

  title varchar(255) NOT NULL default '',

  body longtext NOT NULL,

  teaser longtext NOT NULL,

  log longtext NOT NULL,

  `timestamp` int(11) NOT NULL default '0',

  format int(11) NOT NULL default '0',

  PRIMARY KEY  (vid),

  KEY nid (nid),

  KEY uid (uid)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/в с таблицей node связаны через nid и vid – тот «вид» (ревижен), что записан в таблице node и является «настоящим» (что показываем), а все остальные – это ревизии. Соответственно nid в таблице node уникальный, а в таблице node_revisions уникальный vid /

Теперь таксономия – это своего рода система каталогизирования всех объектов – как оглавление у книги, с учетом того, что разные «страницы» могут иметь разные системы каталогов (в системе – это «словари») и один каталог (словарь) – разные «страницы»
Таблица данных (описания) каталога (каждого отдельно – они все обедняются отдельной таблицей (список словарей - variable) и связываются другой (term_hierarchy), но в этой задаче они не задействованы)

CREATE TABLE term_data (

  tid int(10) unsigned NOT NULL auto_increment,

  vid int(10) unsigned NOT NULL default '0',

  `name` varchar(255) NOT NULL default '',

  description longtext,

  weight tinyint(4) NOT NULL default '0',

  `language` varchar(12) NOT NULL default '',

  trid int(11) NOT NULL default '0',

  PRIMARY KEY  (tid),

  KEY taxonomy_tree (vid,weight,`name`),

  KEY vid_name (vid,`name`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

И последняя – таблица, через которую связываются ноды с терамаи (каталогом – через термы – со словарем)

CREATE TABLE term_node (

  nid int(10) unsigned NOT NULL default '0',

  vid int(10) unsigned NOT NULL default '0',

  tid int(10) unsigned NOT NULL default '0',

  PRIMARY KEY  (tid,vid),

  KEY vid (vid),

  KEY nid (nid)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Теперь 2 переменные, о которых я говорил ранее -

$where_spec_subselect

и

$where_lang_subselect

– это тоже подзапросы, по сути дублирующие подзапросы в секции SELECT, но так как не можем использовать альяс на результат вывода групповой конкатенации в секции WHERE – пришлось их частично продублировать:
Вот так они выглядят в коде:
(SELECT count(td2.name)
FROM term_node as tn2, term_data as td2, node as n2
WHERE td2.vid=4 AND td.tid = ". (intval($search['specialty'])) ." AND tn2.tid = td2.tid AND tn2.vid = n.vid) != 0 AND
и второй:
(SELECT count(td3.name)
FROM term_node as tn3, term_data as td3, node as n3
WHERE td3.vid=5 AND td3.tid = ". (intval($search['ph_language'])) ." AND tn3.tid = td3.tid AND tn3.vid = n.vid) != 0 AND

Они одинаковые, только отличаются словари, по которым выбираются термы.

---------------------------------------

Вот как выглядит запрос в собранном виде (без использования поиска и дополнительной сортировки):

SELECT n.title as `name`, 

        ua.dst as path, 

        n.nid as nid,

        ctph.field_physicians_state_value as state, 

        ctph.field_physicians_city_value as city, 

        IF(

            ISNULL(ctph.field_physicians_city_value) AND ISNULL(ctph.field_physicians_state_value),

            CONCAT(ctph.field_phys_off_city_value, ', ', ctph.field_phys_off_state_value),

            CONCAT(ctph.field_physicians_city_value, ', ', ctph.field_physicians_state_value)) as location,

        ctph.field_physicians_sex_value as gender,

        ctph.field_physicians_phone1_value as phone,

        (SELECT GROUP_CONCAT(' ',td1.name) 

            FROM term_node as tn1, term_data as td1, node as n1 

            WHERE td1.vid=3 AND 

                tn1.nid = n.nid AND 

                tn1.tid = td1.tid AND 

                n1.nid=tn1.nid AND 

                n1.vid=tn1.vid AND 

                n1.`status` <> 0) as degree,

        (SELECT GROUP_CONCAT(' ',td2.name) 

            FROM term_node as tn2, term_data as td2, node as n2 

            WHERE td2.vid=4 AND 

                tn2.nid = n.nid AND 

                tn2.tid = td2.tid AND 

                n2.nid=tn2.nid AND 

                n2.vid=tn2.vid AND 

                n2.`status` <> 0) as specialty,

        (SELECT GROUP_CONCAT(' ',td3.name) 

            FROM term_node as tn3, term_data as td3, node as n3 

            WHERE td3.vid=5 AND 

                tn3.nid = n.nid AND 

                tn3.tid = td3.tid AND 

                n3.nid=tn3.nid AND 

                n3.vid=tn3.vid AND 

                n3.`status` <> 0) as languages

FROM {content_type_physicians} as ctph 

RIGHT JOIN {node} as n ON n.nid = ctph.nid

LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid)

LEFT JOIN {term_node} as tn ON tn.nid = n.nid

LEFT JOIN {term_data} as td ON td.tid = tn.tid

WHERE 1 AND

    td.vid in (3,4,5) AND

    n.language = 'en' AND

    n.vid = ctph.vid AND

    n.`status` <> 0

GROUP BY n.nid

ORDER BY `name` ASC

Почему «…WHERE 1…»? – я всегда так делаю – в самом верху кода переменной where задаю единицу, а затем по надобности в логике скрипта добавляются различные условия (там на форме есть 4 дополнительных поля по которым ищем нужных врачей).

Запрос выполняется в среднем за 0.1-0.14 sec. Что в принципе не мало.
Общее количество врачей сейчас 140. В перспективе будет не более 200-250 (а скорее всего где-то 160-180 на каждом языке).

На счет временных таблиц – пробовал – выходит не менее коряво и по времени также. Я могу ошибаться, но кажется в новых версиях MySQL на подзапросы сам формирует временные таблицы.

На счет нескольких подзапросов с GROUP_CONCAT – дело в том, что нам нужно выбрать (и конкатенировать) из одной таблицы по нескольким условиям. Если мы сделаем без подзапроса, а просто в секции SELECT - GROUP_CONCAT, то это не даст нужного результата (по первому условию он выберет что нужно, а по остальным будет пусто).

Еще раз повторюсь, что, к сожалению, алияс на результат выборки (подзапросов) в секции SELECT нельзя использовать в секции WHERE – пришлось практически продублировать еще раз подзапрос.

P.S.: извините, конечно, если что не так, но искренне жду ваших предложений.

В прикрепленном файле - как выглядит (достаточно просто) это все на странице

Отредактированно ILYA (13.07.2010 19:58:11)

Прикрепленные файлы:

sample_output.png, Размер: 20,359 байт, Скачано: 583

Shopen · 13.07.2010 22:58:25

Выглядит конечно ужасно. Что сразу бросилось в глаза при беглом осмотре

1. "field_physicians_street_value longtext" Не поверю, что это поле и многие другие в таблице content_type_physicians нуждается в таком типе данных (longtext). Скорее всего будет за глаза достаточно varchar(255).
2. LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid) - тут без всякого анализа понятно, что индексы использованы не будут. Если есть возможность нужно в поле url_alias.src отрезать слева node/ , далее сохранять в базу без него, а джойнить напрямую ua.src = n.nid. Либо в таблицу node добавить колонку, где хранить "node/{nid}" и джойнить с этим полем
3. Приведите EXPLAIN итогового запроса, это первое что стоит делать при попытках оптимизации. Запрос может выглядеть очень страшно, но быстро работать, если индесы расставлены верно, а это explain и покажет. Приведите - подумаем дальше

P.S. WHERE 1 я тоже использую, удобно, правда WHERE 1=1, так просто нагляднее

Отредактированно Shopen (13.07.2010 23:03:34)

ILYA · 13.07.2010 23:55:00

Shopen написал:
1. "field_physicians_street_value longtext"

Совершенно верно – достаточно varchar(255), но этот проект на Друпале. В конкретном случае мы не можем отходить от идеологии невмешательства в ядро и сторониие модули системы.

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

Shopen написал:
2. LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid)

На счет url_alias – тоже абсолютно согласен, но поскольку алиас может быть не только к ноду, то соответственно от этого отказаться нельзя. И приходиться использовать достаточно энергоемкую функцию CONCAT('node/',n.nid). Я когда-то пробовал создавать временную таблицу из списка только нодовых альясов (“node/###”) и заполнять ее значениями nid. Использовал при этом LIKE ‘%node%’, что тоже энергоемко. То есть – не спасает, а только усложняет.

Отредактированно ILYA (14.07.2010 12:59:06)

ILYA · 14.07.2010 00:01:39

Shopen написал:
3. Приведите EXPLAIN итогового запроса

Да - конечно:

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

Отредактированно ILYA (14.07.2010 00:04:43)

ILYA · 14.07.2010 12:52:24

Я хотел бы уточнить свой вопрос (просьбу).

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

Меня искренне беспокоят подзапросы (я никогда их не любил – и не полюблю), особенно несколько и, причем, в нескольких секциях.

Другими словами, что можно сделать с этими тремя «…(SELECT GROUP_CONCAT …» ?

А еще с этим – в секции WHERE при поиске (поиске на форме страницы) появляются:
«….(SELECT count(td2.name) FROM term_node as….». То есть – по одному подзапросу для specialty и languages (язык, которыми владеет врач – таксономические термы, а не язык выбираемого нода).

Вот секция WHERE целиком:

WHERE 1 AND td.tid in (8,98)  AND

(SELECT count(td2.name) 

        FROM term_node as tn2, term_data as td2, node as n2 

        WHERE td2.vid=4 AND td.tid = 8 AND tn2.tid = td2.tid AND tn2.vid = n.vid) != 0 AND

(SELECT count(td3.name) 

        FROM term_node as tn3, term_data as td3, node as n3 

        WHERE td3.vid=5 AND td3.tid = 98 AND tn3.tid = td3.tid AND tn3.vid = n.vid) != 0 AND

td.vid in (3,4,5) AND

n.language = 'en' AND

n.vid = ctph.vid AND

n.`status` <> 0

То есть ведите – как оно не хорошо разрастается….

Вот explain запроса с подзапросами в секции WHERE:

http://www.7image.ru/pics/0710/49849939.png

И выполняется такой запрос уже за пол-секунды (0.45 - 0.55 sec)

P.S.: Спасибо всем, кто уделит время, за конструктивные предложения и просто за светлые идеи. С нетерпением жду.

Отредактированно ILYA (14.07.2010 13:00:23)

LazY · 14.07.2010 23:56:25

Shopen написал:
WHERE 1 я тоже использую

Зачем, если не секрет? (какой вообще в этом смысл?)

ILYA · 15.07.2010 00:39:29

Все крайне просто - сам запрос (в данном случае секция WHERE) формируется в несколько этапов, в зависимости от логики добавляются условии. Но может быть, что секция WHERE останется стандартной (выборка по умолчанию). Таким образом проще (быстрее и нагляднее) в самом начале присвоить переменной $where единицу (ну или 1=1 - как кому нравиться) и затем (по ходу скрипта) добавлять $where .= "AND 'новое условие'"; и так далее.

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

Вот и все - все дело в удобстве.

LazY , а что на счет этих ненормальных подзапросов?

Отредактированно ILYA (15.07.2010 00:44:01)

Shopen · 15.07.2010 19:52:20

ILYA написал:
Shopen написал:
3. Приведите EXPLAIN итогового запроса
Да - конечно:
(так как на этом проекте хостинг без ssh, то удобоваримый вид могу предоставить только так)

Как видите не используются идексы в одном объединении. И по сравнению с этим подзапросы - фигня, в плане производительности. Ваш запрос приводит к перебору 800 тысяч строк, тогда как с индексом было бы 22 тысячи (в идеале). Что мешает вам добавить индексированную колонку "node/{nid}" в таблицу node (обновляемую например триггером) и джойнить по ней? Если идеология, то надо решить что вам важнее идеология или скорость. Все CMS - это универсальные ящики, которые как и всё универсальное, плохо работают на узких задачах и их приходится допиливать под себя.

Конструкцию

SELECT ... WHERE 1 AND... AND (SELECT COUNT(*) FROM ...) !=0

имеет смысл переписать так

SELECT ... WHERE 1 AND... AND EXISTS (SELECT 1 FROM ... LIMIT 1)

В подзапросах mysql не будет перебирать все строки, а только до нахождения одной подходящей, вам ведь не важно _сколько_ строк насчитает ваш подзапрос, главное что больше нуля, т.е. достаточно одной.

P.S. И давайте без намеков на "детские разговоры".

Отредактированно Shopen (15.07.2010 19:53:37)

Shopen · 15.07.2010 22:44:14

LazY написал:
Shopen написал:
WHERE 1 я тоже использую
Зачем, если не секрет? (какой вообще в этом смысл?)

Типичная ситуация, когда запрос генерируется скриптом (нотация парсера, но думаю понятно):

SELECT * FROM table

WHERE

  ^hWhere.foreach[k;v]{

      AND $k = '$v'

  }

где в $hWhere - лежат условия для запроса. Если вдруг $hWhere окажется пустым, то запрос получится ошибочным, для этого добавляется в начало 1=1 или просто true. Конечно можно написать так

SELECT * FROM table

^if($hWhere){

  WHERE

  ^hWhere.foreach[k;v]{

      $k = '$v'

  }[ AND ]

}

т.е. AND станет разделителем условий, но во-первых этоменее наглядно (и не уверен, что в других языках такая конструкция вообще возможна), во-вторых бывают и более сложные случаи. В общем универсальнее написать WHERE 1=1 и потом скриптом кидать не думая "AND 'еще одно условие'". Mysql эту единицу проглатывает пулей, и на производительности это никак не сказывается.

ILYA · 15.07.2010 23:47:53

Shopen написал:
Конструкцию
SELECT ... WHERE 1 AND... AND (SELECT COUNT(*) FROM ...) !=0

имеет смысл переписать так
SELECT ... WHERE 1 AND... AND EXISTS (SELECT 1 FROM ... LIMIT 1)

Спасибо - просто и понятно - совершенно верно - ведь нужно всего лишь до первого нахождения. Обязательно нужно будет поправить...

Shopen написал:
добавить индексированную колонку "node/{nid}" в таблицу node

Ммм.... Интересная идея! Спасибо за нее отдельное - нужно будет попробовать. И между прочим, если принять за правило - то это достаточно не тяжело реализовать. Сама таблица node несет основные данные по контенту и поэтому при любом обновлении системы она меняться не будет (более чем уверен) - поэтому все нормально. Выборка на альясы используется, по ходу, достаточно часто - поэтому можно ее практически продублировать в таблице node, но индексированное только src (то, где записано node/nid).
А обновлять можно тригером, а можно в перехвате (hook_nodeapi по операциям inser/update) в отдельном модуле (это уже нюансы...)

Другими словами, Shopen спасибо - все варианты попробую.

Shopen написал:
детские разговоры

Замете - фразы уже давно нет.

Отредактированно ILYA (15.07.2010 23:49:16)

Shopen · 16.07.2010 00:02:41

ILYA написал:
Shopen написал:
добавить индексированную колонку "node/{nid}" в таблицу node
Ммм.... Интересная идея! Спасибо за нее отдельное - нужно будет попробовать.

Я ее вам еще в предыдущем сообщении написал, пропустили?

ILYA написал:
Замете - фразы уже давно нет.

Да, заметил, но слово не воробей. Впрочем забудем об этом.

Попробуйте эти оптимизации и сообщите результат, интересно.

ILYA · 16.07.2010 14:35:06

После изменения в секции WHERE (с использованием ... AND EXISTS (SELECT 1 FROM ... LIMIT 1))
Время сократилось до полсекунды (0.50- 0.52 sec) – было полторы секунды.

После обновления структуры таблицы node (добавления индексного поля src, которое содержит “node/nid”) – время выполнения 0.45 sec.

Shopen написал:
Я ее вам еще в предыдущем сообщении написал, пропустили?

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

Вот сам конечный запрос:

SELECT n.title as `name`, 

        ua.dst as path, 

        n.nid as nid,

        ctph.field_physicians_state_value as state, 

        ctph.field_physicians_city_value as city, 

        IF(

            ISNULL(ctph.field_physicians_city_value) AND ISNULL(ctph.field_physicians_state_value),

            CONCAT(ctph.field_phys_off_city_value, ', ', ctph.field_phys_off_state_value),

            CONCAT(ctph.field_physicians_city_value, ', ', ctph.field_physicians_state_value)) as location,

        ctph.field_physicians_sex_value as gender,

        ctph.field_physicians_phone1_value as phone,

        (SELECT GROUP_CONCAT(' ',td1.name) 

            FROM term_node as tn1, term_data as td1, node as n1 

            WHERE td1.vid=3 AND 

                tn1.nid = n.nid AND 

                tn1.tid = td1.tid AND 

                n1.nid=tn1.nid AND 

                n1.vid=tn1.vid AND 

                n1.`status` <> 0) as degree,

        (SELECT GROUP_CONCAT(' ',td2.name) 

            FROM term_node as tn2, term_data as td2, node as n2 

            WHERE td2.vid=4 AND 

                tn2.nid = n.nid AND 

                tn2.tid = td2.tid AND 

                n2.nid=tn2.nid AND 

                n2.vid=tn2.vid AND 

                n2.`status` <> 0) as specialty,

        (SELECT GROUP_CONCAT(' ',td3.name) 

            FROM term_node as tn3, term_data as td3, node as n3 

            WHERE td3.vid=5 AND 

                tn3.nid = n.nid AND 

                tn3.tid = td3.tid AND 

                n3.nid=tn3.nid AND 

                n3.vid=tn3.vid AND 

                n3.`status` <> 0) as languages

FROM content_type_physicians as ctph 

RIGHT JOIN node as n ON n.nid = ctph.nid

LEFT JOIN url_alias as ua ON ua.src = n.src

LEFT JOIN term_node as tn ON tn.nid = n.nid

LEFT JOIN term_data as td ON td.tid = tn.tid

WHERE 1 AND td.tid in (8,98)  AND

EXISTS (SELECT 1 FROM term_node as tn2, term_data as td2, node as n2 WHERE td2.vid=4 AND td.tid = 8 AND tn2.tid = td2.tid AND tn2.vid = n.vid LIMIT 1) AND

EXISTS (SELECT 1 FROM term_node as tn3, term_data as td3, node as n3 WHERE td3.vid=5 AND td3.tid = 98 AND tn3.tid = td3.tid AND tn3.vid = n.vid LIMIT 1) AND

td.vid in (3,4,5) AND

n.language = 'en' AND

n.vid = ctph.vid AND

n.`status` <> 0

GROUP BY n.nid

ORDER BY `name` ASC

Структура таблицы node:

CREATE TABLE node (

  nid int(10) unsigned NOT NULL auto_increment,

  vid int(10) unsigned NOT NULL default '0',

  `type` varchar(32) NOT NULL default '',

  `language` varchar(12) NOT NULL default '',

  title varchar(255) NOT NULL default '',

  uid int(11) NOT NULL default '0',

  `status` int(11) NOT NULL default '1',

  created int(11) NOT NULL default '0',

  `changed` int(11) NOT NULL default '0',

  `comment` int(11) NOT NULL default '0',

  promote int(11) NOT NULL default '0',

  moderate int(11) NOT NULL default '0',

  sticky int(11) NOT NULL default '0',

  tnid int(10) unsigned NOT NULL default '0',

  translate int(11) NOT NULL default '0',

  src varchar(255) NOT NULL,

  PRIMARY KEY  (nid),

  UNIQUE KEY vid (vid),

  KEY node_changed (`changed`),

  KEY node_created (created),

  KEY node_moderate (moderate),

  KEY node_promote_status (promote,`status`),

  KEY node_status_type (`status`,`type`,nid),

  KEY node_title_type (title,`type`(4)),

  KEY node_type (`type`(4)),

  KEY uid (uid),

  KEY tnid (tnid),

  KEY translate (translate),

  KEY src (src)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=624 ;

EXPLAYN:

http://www.7image.ru/pics/0710/5036035.png

И тут у меня вопрос в отношении ключа src_language_pid (key_len = 386) – кажется это тоже можно «улучшить»?

Отредактированно ILYA (16.07.2010 14:36:17)

ILYA · 16.07.2010 14:39:19

И еще: чуть позже я уменьшил размерность для src в таблице node
С varchar(255) до varchar(32) . Этого более чем достаточно будет...

Shopen · 25.07.2010 17:23:39

ILYA написал:
И тут у меня вопрос в отношении ключа src_language_pid (key_len = 386) – кажется это тоже можно «улучшить»?

Можно, прироста это все равно большого не даст. У вас там тоже longtext в этом поле?

Больше очевидного не вижу, но поэкспериментировать можно, имея данные на руках, например я бы добавил индекс в term_data PRIMARY KEY (tid, vid), чтобы избавится от range.

ILYA · 30.07.2010 18:09:50

Спасибо за ответы.
С практической точки зрения (в смысле - не изменяя структур таблиц) - мне очень понравилась

SELECT ... WHERE 1 AND... AND EXISTS (SELECT 1 FROM ... LIMIT 1)

теперь активно использую.
Также добавив индексированное поле по src в таблицу node я думаю прирост производительности по сути из-за отказа от CONCAT (все же - строковая операция...), но это я так думаю - могу ошибаться (ну... тоесть, я думаю, что прирост производительности был в "этом месте" не из-за индексированного поля... - то есть, не только из-за него).

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

Еще раз спасибо.

Shopen · 30.07.2010 23:09:53

ILYA написал:
Также добавив индексированное поле по src в таблицу node я думаю прирост производительности по сути из-за отказа от CONCAT (все же - строковая операция...), но это я так думаю - могу ошибаться (ну... тоесть, я думаю, что прирост производительности был в "этом месте" не из-за индексированного поля... - то есть, не только из-за него).

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

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

Еще раз спасибо.

Пожалуйста, а что с range - не стали оптимизировать?

Форум пользователей MySQL

#1 13.07.2010 19:38:14

Оптимизировать запрос с несколькими подзапросами

#2 13.07.2010 22:58:25

Re: Оптимизировать запрос с несколькими подзапросами

#3 13.07.2010 23:55:00

Re: Оптимизировать запрос с несколькими подзапросами

Shopen написал:

Shopen написал:

#4 14.07.2010 00:01:39

Re: Оптимизировать запрос с несколькими подзапросами

Shopen написал:

#5 14.07.2010 12:52:24

Re: Оптимизировать запрос с несколькими подзапросами

#6 14.07.2010 23:56:25

Re: Оптимизировать запрос с несколькими подзапросами

Shopen написал:

#7 15.07.2010 00:39:29

Re: Оптимизировать запрос с несколькими подзапросами

#8 15.07.2010 19:52:20

Re: Оптимизировать запрос с несколькими подзапросами

ILYA написал:

Shopen написал:

#9 15.07.2010 22:44:14

Re: Оптимизировать запрос с несколькими подзапросами

LazY написал:

Shopen написал:

#10 15.07.2010 23:47:53

Re: Оптимизировать запрос с несколькими подзапросами

Shopen написал:

Shopen написал:

Shopen написал:

#11 16.07.2010 00:02:41

Re: Оптимизировать запрос с несколькими подзапросами

ILYA написал:

Shopen написал:

ILYA написал:

#12 16.07.2010 14:35:06

Re: Оптимизировать запрос с несколькими подзапросами

Shopen написал:

#13 16.07.2010 14:39:19

Re: Оптимизировать запрос с несколькими подзапросами

#14 25.07.2010 17:23:39

Re: Оптимизировать запрос с несколькими подзапросами

ILYA написал:

#15 30.07.2010 18:09:50

Re: Оптимизировать запрос с несколькими подзапросами

#16 30.07.2010 23:09:53

Re: Оптимизировать запрос с несколькими подзапросами

ILYA написал:

ILYA написал:

Board footer