[help] Запрос виснет

Тема в разделе "Базы данных", создана пользователем HatoL, 21 июл 2008.

Статус темы:
Закрыта.
Модераторы: latteo
  1. HatoL

    HatoL

    Регистр.:
    5 фев 2008
    Сообщения:
    206
    Симпатии:
    36
    Вообщем проблема в следующем. Есть рабочий запрос, который выводит нужные мне данные:
    Код:
    SELECT `games_pl`.* , `games`.* ,
    					`u11`.`ggcacc` AS `u11` ,
    					`u12`.`ggcacc` AS `u12` ,
    					`u13`.`ggcacc` AS `u13` ,
    					`u14`.`ggcacc` AS `u14` ,
    					`u15`.`ggcacc` AS `u15` ,
    					`u21`.`ggcacc` AS `u21` ,
    					`u22`.`ggcacc` AS `u22` ,
    					`u23`.`ggcacc` AS `u23` ,
    					`u24`.`ggcacc` AS `u24` ,
    					`u25`.`ggcacc` AS `u25` ,
    					`l11`.`ggc_loc` AS `loc11` ,
    					`l12`.`ggc_loc` AS `loc12` ,
    					`l13`.`ggc_loc` AS `loc13` ,
    					`l14`.`ggc_loc` AS `loc14` ,
    					`l15`.`ggc_loc` AS `loc15` ,
    					`l21`.`ggc_loc` AS `loc21` ,
    					`l22`.`ggc_loc` AS `loc22` ,
    					`l23`.`ggc_loc` AS `loc23` ,
    					`l24`.`ggc_loc` AS `loc24` ,
    					`l25`.`ggc_loc` AS `loc25`
    					FROM `games_pl` , `games` ,
    					`profiles` AS `l11` ,
    					`profiles` AS `l12` ,
    					`profiles` AS `l13` ,
    					`profiles` AS `l14` ,
    					`profiles` AS `l15` ,
    					`profiles` AS `l21` ,
    					`profiles` AS `l22` ,
    					`profiles` AS `l23` ,
    					`profiles` AS `l24` ,
    					`profiles` AS `l25` ,
    					`userlist` AS `u11` ,
    					`userlist` AS `u12` ,
    					`userlist` AS `u13` ,
    					`userlist` AS `u14` ,
    					`userlist` AS `u15` ,
    					`userlist` AS `u21` ,
    					`userlist` AS `u22` ,
    					`userlist` AS `u23` ,
    					`userlist` AS `u24` ,
    					`userlist` AS `u25`
    					WHERE
    					((`games_pl`.`p11` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p12` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p13` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p14` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p15` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p21` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p22` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p23` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p24` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p25` = ' . RIHL_PLAYER_ID . ')) AND
    					(`games_pl`.`id` = `games`.`id`) AND
    					(`u11`.`player_id` = `games_pl`.`p11`) AND
    					(`u12`.`player_id` = `games_pl`.`p12`) AND
    					(`u13`.`player_id` = `games_pl`.`p13`) AND
    					(`u14`.`player_id` = `games_pl`.`p14`) AND
    					(`u15`.`player_id` = `games_pl`.`p15`) AND
    					(`u21`.`player_id` = `games_pl`.`p21`) AND
    					(`u22`.`player_id` = `games_pl`.`p22`) AND
    					(`u23`.`player_id` = `games_pl`.`p23`) AND
    					(`u24`.`player_id` = `games_pl`.`p24`) AND
    					(`u25`.`player_id` = `games_pl`.`p25`) AND
    					(`u11`.`player_id` = `l11`.`player_id`) AND
    					(`u12`.`player_id` = `l12`.`player_id`) AND
    					(`u13`.`player_id` = `l13`.`player_id`) AND
    					(`u14`.`player_id` = `l14`.`player_id`) AND
    					(`u15`.`player_id` = `l15`.`player_id`) AND
    					(`u21`.`player_id` = `l21`.`player_id`) AND
    					(`u22`.`player_id` = `l22`.`player_id`) AND
    					(`u23`.`player_id` = `l23`.`player_id`) AND
    					(`u24`.`player_id` = `l24`.`player_id`) AND
    					(`u25`.`player_id` = `l25`.`player_id`)
    					ORDER BY `games`.`START` DESC)
    (RIHL_PLAYER_ID - константа в PHP). Этот запрос впринципе выполняется довольно быстро (0.1-0.2 секунды на моей 1.8 ГГц машине). Но он выводит не все данные. Дело в том, что поля `p14`,`p15` и `p24`,`p25` из таблицы `games_pl` могут содержать значение 0. А в моем запросе выбираются только те записи, у которых все поля приравниваются к полю `player_id` таблицы `userlist`. Я пробовал изменить запрос, добавив записи OR, но тогда система зависает, а в чем дело я понять не могу. Вот измененный запрос:
    Код:
    SELECT `games_pl`.* , `games`.* ,
    					`u11`.`ggcacc` AS `u11` ,
    					`u12`.`ggcacc` AS `u12` ,
    					`u13`.`ggcacc` AS `u13` ,
    					`u14`.`ggcacc` AS `u14` ,
    					`u15`.`ggcacc` AS `u15` ,
    					`u21`.`ggcacc` AS `u21` ,
    					`u22`.`ggcacc` AS `u22` ,
    					`u23`.`ggcacc` AS `u23` ,
    					`u24`.`ggcacc` AS `u24` ,
    					`u25`.`ggcacc` AS `u25` ,
    					`l11`.`ggc_loc` AS `loc11` ,
    					`l12`.`ggc_loc` AS `loc12` ,
    					`l13`.`ggc_loc` AS `loc13` ,
    					`l14`.`ggc_loc` AS `loc14` ,
    					`l15`.`ggc_loc` AS `loc15` ,
    					`l21`.`ggc_loc` AS `loc21` ,
    					`l22`.`ggc_loc` AS `loc22` ,
    					`l23`.`ggc_loc` AS `loc23` ,
    					`l24`.`ggc_loc` AS `loc24` ,
    					`l25`.`ggc_loc` AS `loc25`
    					FROM `games_pl` , `games` ,
    					`profiles` AS `l11` ,
    					`profiles` AS `l12` ,
    					`profiles` AS `l13` ,
    					`profiles` AS `l14` ,
    					`profiles` AS `l15` ,
    					`profiles` AS `l21` ,
    					`profiles` AS `l22` ,
    					`profiles` AS `l23` ,
    					`profiles` AS `l24` ,
    					`profiles` AS `l25` ,
    					`userlist` AS `u11` ,
    					`userlist` AS `u12` ,
    					`userlist` AS `u13` ,
    					`userlist` AS `u14` ,
    					`userlist` AS `u15` ,
    					`userlist` AS `u21` ,
    					`userlist` AS `u22` ,
    					`userlist` AS `u23` ,
    					`userlist` AS `u24` ,
    					`userlist` AS `u25`
    					WHERE
    					((`games_pl`.`p11` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p12` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p13` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p14` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p15` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p21` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p22` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p23` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p24` = ' . RIHL_PLAYER_ID . ') OR
    					(`games_pl`.`p25` = ' . RIHL_PLAYER_ID . ')) AND
    					(`games_pl`.`id` = `games`.`id`) AND
    					(`u11`.`player_id` = `games_pl`.`p11`) AND
    					(`u12`.`player_id` = `games_pl`.`p12`) AND
    					(`u13`.`player_id` = `games_pl`.`p13`) AND
    					((`u14`.`player_id` = `games_pl`.`p14`) OR `games_pl`.`p14` = 0) AND
    					((`u15`.`player_id` = `games_pl`.`p15`) OR `games_pl`.`p15` = 0) AND
    					(`u21`.`player_id` = `games_pl`.`p21`) AND
    					(`u22`.`player_id` = `games_pl`.`p22`) AND
    					(`u23`.`player_id` = `games_pl`.`p23`) AND
    					((`u24`.`player_id` = `games_pl`.`p24`) OR `games_pl`.`p24` = 0) AND
    					((`u25`.`player_id` = `games_pl`.`p25`) OR `games_pl`.`p25` = 0) AND
    					(`u11`.`player_id` = `l11`.`player_id`) AND
    					(`u12`.`player_id` = `l12`.`player_id`) AND
    					(`u13`.`player_id` = `l13`.`player_id`) AND
    					(`u14`.`player_id` = `l14`.`player_id`) AND
    					(`u15`.`player_id` = `l15`.`player_id`) AND
    					(`u21`.`player_id` = `l21`.`player_id`) AND
    					(`u22`.`player_id` = `l22`.`player_id`) AND
    					(`u23`.`player_id` = `l23`.`player_id`) AND
    					(`u24`.`player_id` = `l24`.`player_id`) AND
    					(`u25`.`player_id` = `l25`.`player_id`)
    					ORDER BY `games`.`START` DESC
     
  2. awp

    awp Читатель

    Заблокирован
    Регистр.:
    20 июл 2008
    Сообщения:
    7
    Симпатии:
    2
    пробуй через join - он всегда быстрее работал.
     
  3. corehardcoder

    corehardcoder Создатель

    Регистр.:
    29 июн 2008
    Сообщения:
    20
    Симпатии:
    2
    Зависает полностью или просто долго выполняется? Если второе - то вполне логично, так как очень много таблиц объединяется. Память сжирается мгновенно и начинает юзаться своп.
     
Статус темы:
Закрыта.