LEFT JOIN завалил диск...

[zEitEr]

LEFT JOIN завалил диск...

Доброо дня!

Подскажите, чего не так сделал:

SELECT `t1` . * , `pt`.`page_desc` , `pt`.`page_text` , 
`t2`.`page_id` AS `next_page_id` , `t2`.`page_title` AS `next_page_title` , 
`t3`.`page_id` AS `prev_page_id` , `t3`.`page_title` AS `prev_page_title` 
FROM `pages` AS `t1` 
LEFT JOIN `pages` AS `t2` ON ( `t2`.`page_id` > `t1`.`page_id` 
AND `t2`.`page_type` = '1'
AND `t2`.`is_active` != '0' ) 
LEFT JOIN `pages` AS `t3` ON ( `t3`.`page_id` < `t1`.`page_id` 
AND `t3`.`page_type` = '1'
AND `t3`.`is_active` != '0' ) 
LEFT JOIN `pages_text` AS `pt` ON ( `pt`.`page_id` = `t1`.`page_id` ) 
WHERE `t1`.`page_id` = '320'
AND `t1`.`page_type` = '1'
AND `t1`.`is_active` != '0'
ORDER BY `t3`.`page_id` DESC 
LIMIT 1

При активной посещаемости страницы, на диске во временной директории mysql, образовалось временных файлов размеров 42Гб...

В таблице `pages`:
Данные 71,332 Байт
Индекс 96,256 Байт
Всего 163.7 КБ
Строки 727

В таблице `page_text`:
Данные 3,718.0 КБ
Индекс 2,472.0 КБ
Всего 6,190.0 КБ
Строки 727

Беглый просмотр файла с временными данными показал, что в нем данные из `page_text`.
Видимо эту таблицу сервер MySQL из-за LEFT JOIN перелапачивает несколько раз.
Вот как запрос тогда оптимально переписать? Или вообще экономичнее будет три запроса отдельных делать?

-~{}~ 13.02.09 18:23:

Один такой запрос создает временный файл на диске размером ~600Мб О_о
Жесть...

 

[DiMA]

- Доктор, когда я делаю ТАК, у меня отваливается ЗДЕСЬ. Подскажите, что делать?
- А вы не делайте ТАК

нафига условия в ON засунул, а не в WHERE?

 

[zEitEr]

Заценил шутку)))

Переписал запрос слуедующим образом:

SELECT `pt`.`page_desc` , `pt`.`page_text` , `t1` . * , 
`t2`.`page_id` AS `next_page_id` , `t2`.`page_title` AS `next_page_title` , 
`t3`.`page_id` AS `prev_page_id` , `t3`.`page_title` AS `prev_page_title` 
FROM `pages` AS `t1` 
JOIN `pages_text` AS `pt` 
LEFT JOIN `pages` AS `t2` ON ( `t2`.`page_id` > `t1`.`page_id` 
AND `t2`.`page_type` = '1'
AND `t2`.`is_active` != '0' ) 
LEFT JOIN `pages` AS `t3` ON ( `t3`.`page_id` < `t1`.`page_id` 
AND `t3`.`page_type` = '1'
AND `t3`.`is_active` != '0' ) 
WHERE `t1`.`page_id` = '310'
AND `t1`.`page_type` = '1'
AND `t1`.`is_active` != '0'
AND `t1`.`page_id` = `pt`.`page_id` 
ORDER BY `t3`.`page_id` DESC 
LIMIT 1

Можно как-то еще оптимизировать?
На диске все так же создается файлик, но уже размером ~30Мб )))

-~{}~ 13.02.09 18:41:

нафига условия в ON засунул, а не в WHERE?

Потому что иначе, если `page_id` = MAX(`page_id`), то запрос возращается пустой:

SELECT `pt`.`page_desc` , `pt`.`page_text` , `t1` . * , 
`t2`.`page_id` AS `next_page_id` , `t2`.`page_title` AS `next_page_title` , 
`t3`.`page_id` AS `prev_page_id` , `t3`.`page_title` AS `prev_page_title` 
FROM `pages` AS `t1` 
JOIN `pages_text` AS `pt` 
LEFT JOIN `pages` AS `t2` ON ( `t2`.`page_id` > `t1`.`page_id` ) 
LEFT JOIN `pages` AS `t3` ON ( `t3`.`page_id` < `t1`.`page_id` ) 
WHERE `t1`.`page_id` = '727'
AND `t1`.`page_type` = '1'
AND `t1`.`is_active` != '0'
AND `t1`.`page_id` = `pt`.`page_id`
AND `t2`.`page_type` = '1'
AND `t2`.`is_active` != '0'
AND `t3`.`page_type` = '1'
AND `t3`.`is_active` != '0'
ORDER BY `t3`.`page_id` DESC 
LIMIT 1

MySQL вернула пустой результат (т.е. ноль строк). ( запрос занял 0.0008 сек. )

 

[DiMA]

покажи структуру таблиц
почему не сделать 2 или 3 запроса?
если 0 строк - удаляй последовательно условия, ищи в чем затык

 

[zEitEr]

Так я и спросил в первом посте

Или вообще экономичнее будет три запроса отдельных делать?

 

[DiMA]

JOIN `pages_text` AS `pt` - почему здесь условия нет?

я такие задачи решаю в 1 или 2 запроса на поиск страницы и предыдущего/следующего элемента
и доп. запрос по выборке всех недостающих фраз по найденным ID из таблицы со словарем

 

[zEitEr]

покажи структуру таблиц

Вот:

CREATE TABLE IF NOT EXISTS `pages_text` (
  `page_id` mediumint(8) unsigned NOT NULL default '0',
  `page_desc` mediumtext NOT NULL,
  `page_text` text,
  PRIMARY KEY  (`page_id`),
  FULLTEXT KEY `page_text_desc` (`page_desc`,`page_text`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

и

CREATE TABLE IF NOT EXISTS `pages` (
  `page_id` int(11) unsigned NOT NULL auto_increment,
  `page_left` int(11) unsigned NOT NULL default '0',
  `page_right` int(11) unsigned NOT NULL default '0',
  `page_level` int(11) unsigned NOT NULL default '0',
  `page_title` varchar(255) NOT NULL default '',

......skipped.......

  `page_type` tinyint(2) NOT NULL default '0',

......skipped.......

  `is_active` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`page_id`),
  KEY `page_left` (`page_left`,`page_right`,`page_level`),
  KEY `page_title` (`page_title`),
  KEY `page_type` (`page_type`),
  KEY `is_active` (`is_active`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251

-~{}~ 13.02.09 20:23:

Автор оригинала: DiMA
JOIN `pages_text` AS `pt` - почему здесь условия нет?

Оно вынесено в общее WHERE

Автор оригинала: DiMA я такие задачи решаю в 1 или 2 запроса на поиск страницы и предыдущего/следующего элемента
и доп. запрос по выборке всех недостающих фраз по найденным ID из таблицы со словарем

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

 

[DIS]

что за временные файлы там создаются?

очевидно, что это FULLTEXT KEY `page_text_desc` (`page_desc`,`page_text`)

я бы грохнул индекс и погладел бы что будет..

хотя у себя на серваке я никаких временных файлов кроме логов не вижу.

 

[zEitEr]

Автор оригинала: DIS
что за временные файлы там создаются?

В настройках MySQL стоит ограничение на размер временных таблиц, очевидно, что когда этот лимит превышается, временные таблицы пишутся во временные файлы в директорию:

set-variable    = tmpdir=/usr/local/mysql/tmp

Пришлось вынести в другой раздел, т.к. в разделах /tmp и /var/tmp не было достаточного места.

очевидно, что это FULLTEXT KEY `page_text_desc` (`page_desc`,`page_text`)

Вполне возможно, но при беглом просмотре, я там видел полные тексты из `pages_text`.`page_text` + данные из других таблиц.

я бы грохнул индекс и погладел бы что будет..

Пока проблематично это сделать - при наличии времени - проверю...

хотя у себя на серваке я никаких временных файлов кроме логов не вижу.

А смотрите в правильной директории? У меня в тот момент был массовый "прессинг" со стороны поисковых ботов: гугл, яндекс, яха и несколько других...