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

lexikon · 23.09.2013 13:29:54

Здравствуйте. Прошу помощи у экспертов
есть два запроса, во обоих используется UNION ALL для того чтобы избежать условий в JOIN, хотелось бы оптимизировать оба:
$user_id - ID пользователя
1) Данный запрос извлекает всех одноклассников(личную информацию), информацию о классе и информацию в друзъях ли данный одноклассник. Далее группирует, т.к. результатом UNION будет 2 одинковых записи с отличными полями из таблицы mw_friend_requests (в одном случае будут не пустые, в другом поля будут NULL).

SELECT * FROM ((SELECT c.*, 

     u.name,    u.surname, u.patronymic, u.email, u.reg_date, u.phone, u.country, u.city, 

     u.avatar_ext, u.position, u.job, u.scope, s.name as 'class_name', s.title as 'class_title', 

     s.active as 'class_active', fr.status, fr.date 

 FROM mw_userclasses c 

 INNER JOIN mw_users u

    ON c.user_id = u.id

 INNER JOIN mw_classes s

    ON c.class_id = s.id

 LEFT OUTER JOIN mw_friend_requests fr

    ON c.user_id = fr.receiver_id

 WHERE class_id IN ($user_classes) AND user_id != $user_id

)UNION ALL (

 SELECT c.*, 

    u.name, u.surname, u.patronymic, u.email, u.reg_date, u.phone, u.country, u.city, 

    u.avatar_ext, u.position, u.job, u.scope, s.name as 'class_name', s.title as 'class_title', 

    s.active as 'class_active', fr.status, fr.date FROM mw_userclasses c 

 INNER JOIN mw_users u

    ON c.user_id = u.id

 INNER JOIN mw_classes s

    ON c.class_id = s.id

 LEFT OUTER JOIN mw_friend_requests fr

    ON c.user_id = fr.sender_id

 WHERE class_id IN ($user_classes) AND user_id != $user_id

)) t

GROUP BY `user_id` ORDER BY entry_date ASC;

2) Данный запрос извлекает список друзей (подтвержденных предложений дружбы = fr.status)

(SELECT u.*, 

        fr.offer_id, 

        fr.sender_id, 

        fr.receiver_id, 

        fr.date, 

        fr.status

 FROM {$prefix}_friend_requests fr

 INNER JOIN {$prefix}_users u ON u.id = fr.sender_id

 WHERE (fr.sender_id = $user_id OR fr.receiver_id = $user_id) AND u.id != $user_id AND fr.status = 1

) UNION ALL (

 SELECT u.*, 

        fr.offer_id, 

        fr.sender_id, 

        fr.receiver_id, 

        fr.date, 

        fr.status

 FROM {$prefix}_friend_requests fr

 INNER JOIN {$prefix}_users u ON u.id = fr.receiver_id

 WHERE (fr.sender_id = $user_id OR fr.receiver_id = $user_id) AND u.id != $user_id AND fr.status = 1

)

ORDER BY status, date ASC;

Данные о таблицах:

CREATE TABLE IF NOT EXISTS `mw_friend_requests` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `offer_id` int(11) NOT NULL,

  `sender_id` int(11) NOT NULL,

  `receiver_id` int(11) NOT NULL,

  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `status` tinyint(4) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `sender_id` (`sender_id`),

  KEY `receiver_id` (`receiver_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `mw_userclasses` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) NOT NULL,

  `class_id` int(11) NOT NULL,

  `entry_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `a` int(11) NOT NULL,

  `b` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `user_id` (`user_id`,`class_id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Заранее благодарю за помощь

Отредактированно lexikon (23.09.2013 13:50:03)

Александр Трофимов · 23.09.2013 13:41:02

Я вижу user_id!=$user_id, но не вижу user_id = $user_id.
Это выборка всех одноклассников для конкретного одноклассника или выборка всех одноклассников для всех одноклассников, кроме конкретного одноклассника?

Отредактированно Александр Трофимов (23.09.2013 13:41:24)

lexikon · 23.09.2013 13:42:17

всех одноклассников для пользователя, user_id != $user_id для того чтобы исключить из выборки себя самого

Александр Трофимов · 23.09.2013 13:48:36

LEFT OUTER JOIN mw_friend_requests fr

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

lexikon · 23.09.2013 13:52:32

верно, путем использования UNION в одном случае возвращаются поля NULL, в другом со значениями (status)...после чего группирую по user_id

UNION можно было бы заменить конструкцией
LEFT OUTER JOIN mw_friend_requests fr ON c.user_id = fr.receiver_id OR c.user_id = fr.sender_id
но использовать OR в джоине не хочется(да и судя по результатам будет работать дольше)

Отредактированно lexikon (23.09.2013 13:54:44)

Александр Трофимов · 23.09.2013 14:16:06

Я бы пошел другим путем и начал бы с получения ID одноклассников. К таблице с ID одноклассников подвязал бы все остальное. Если их нет, то результат будет пустым.

Что-то вроде этого:

SELECT

    c.*,

    u.name,

    u.surname,

    u.patronymic,

    u.email,

    u.reg_date,

    u.phone,

    u.country,

    u.city,

    u.avatar_ext,

    u.position,

    u.job,

    u.scope,

    s.name as 'class_name',

    s.title as 'class_title',

    s.active as 'class_active', 

    fr.status,

    fr.date   

FROM

(

SELECT

    `sender_id` as user_id,

   ` status`,

    `date`

FROM

    mw_friend_requests

WHERE

    receiver_id = $user_id

UNION SELECT

    `receiver_id` as user_id,

   ` status`,

    `date`

FROM

    mw_friend_requests

WHERE

    `sender_id` = $user_id

) as fr

JOIN mw_users u ON (fr.user_id = u.id)

JOIN mw_userclasses c ON (fr.user_id = c.id)

JOIN mw_classes s ON (c.class_id = s.id)

WHERE 

     class_id IN ($user_classes)

ORDER BY c.entry_date

Отредактированно Александр Трофимов (23.09.2013 14:28:14)

Александр Трофимов · 23.09.2013 14:22:45

Кажется выше получился запрос по всем друзьям, а не одноклассникам. +)))

lexikon · 23.09.2013 14:23:04

#1222 - The used SELECT statements have a different number of columns

благодарю, но не могу запустить запрос чтобы это значило? и относительно какого запроса из топа касается - №1 или №2?

Отредактированно lexikon (23.09.2013 14:28:16)

Александр Трофимов · 23.09.2013 14:30:16

lexikon написал:
#1222 - The used SELECT statements have a different number of columns
благодарю, но не могу запустить запрос чтобы это значило? и относительно какого запроса из топа касается - №1 или №2?

Это значит, что в UNION количество выбранных колонок в одном из запросов не совпадает с количеством выбранных колонок в следующем запросе.
Исправил. Но факт остается фактом — запрос выбирает только друзей. +)

lexikon · 23.09.2013 14:34:19

Александр Трофимов написал:
Это значит, что в UNION количество выбранных колонок в одном из запросов не совпадает с количеством выбранных колонок в следующем запросе.
Исправил. Но факт остается фактом — запрос выбирает только друзей. +)

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

Александр Трофимов · 23.09.2013 14:41:35

SELECT

    c.*,

    u.name,

    u.surname,

    u.patronymic,

    u.email,

    u.reg_date,

    u.phone,

    u.country,

    u.city,

    u.avatar_ext,

    u.position,

    u.job,

    u.scope,

    s.name as 'class_name',

    s.title as 'class_title',

    s.active as 'class_active',

    IFNULL(fr.status,0) as status,

    IFNULL(fr.date,0) as `date`,

    IFNULL(fr.relationship_type,'') as relationship_type

FROM

    mw_userclasses c

    JOIN mw_users u ON (c.user_id = u.id)

    JOIN mw_classes s ON (c.class_id = s.id)

    LEFT JOIN (

        SELECT

            `sender_id` as user_id,

           ` status`,

            `date`,

             'sender' as relationship_type

        FROM

            mw_friend_requests

        WHERE

            receiver_id = $user_id

        UNION SELECT

            `receiver_id` as user_id,

            ` status`,

            `date`,

            'receiver' as relationship_type

        FROM

            mw_friend_requests

        WHERE

            `sender_id` = $user_id

    ) fr ON (fr.user_id=c.user_id)

WHERE

    c.class_id IN ($user_classes)

    AND c.user_id != $user_id

ORDER BY c.entry_date

Подход к таблице с друзьями остался, мы создаем временную таблицу со всеми друзьями и подключаем ее к запросу со всеми одноклассниками. Если статус relationship_type=='receiver', значит этот одноклассник получил приглашение от меня, если 'sender', значит одноклассник пригласил сам.
Остается открытым вопрос по таблице mw_friend_requests: могут ли там быть одинаковых записи с разными датами и статусами вида sender_id (1),receiver_id(2) == sender_id (2),receiver_id(1). Если с одним другом может быть несколько связей в разных направлениях, то надо добавлять группировку в подзапрос, чтобы получить уникальные user_id.

Отредактированно Александр Трофимов (23.09.2013 14:59:37)

Александр Трофимов · 23.09.2013 14:50:34

lexikon написал:
Александр Трофимов написал:
Это значит, что в UNION количество выбранных колонок в одном из запросов не совпадает с количеством выбранных колонок в следующем запросе.
Исправил. Но факт остается фактом — запрос выбирает только друзей. +)
запрос вообще ничего не вернул )))
ну и да, надо выбрать не друзей, а всех одноклассников, и понять кто делал предложение в друзья мне, а кому я, а кто вовсе не делал никаких предложений

Не помню, как реагирует UNION если один из запросов выдает пустой ответ.
Можешь запустить только подзапрос и посмотреть будет ли ответ?

Александр Трофимов · 23.09.2013 14:59:02

И да,

AND user_id != $user_id

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

Александр Трофимов · 23.09.2013 15:14:43

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

SELECT

    u.*,

    fr.offer_id,

    fr.friend_id,

    fr.status,

    fr.date,

    IFNULL(fr.relationship_type,'') as relationship_type  

FROM

(

SELECT

    `offer_id`,

    `sender_id` as user_id,

    `receiver_id` as friend_id,

    `date`,

    'sender' as relationship_type

FROM

    mw_friend_requests

WHERE

    sender_id = $user_id

    AND `status` = 1

UNION SELECT

    `offer_id`,

    `receiver_id` as user_id,

    `sender_id` as friend_id,

    `date`,

    'receiver' as relationship_type 

FROM

    mw_friend_requests

WHERE

    `receiver_id` = $user_id

    AND `status` = 1

) as fr

JOIN mw_users u ON (fr.friend_id = u.id)

ORDER BY fr.date

fr.status выбирать не имеет смысла, потому-что он по условию равен 1.
fr.user_id выбирать не имеет смысла, он всегда равен $user_id.
Если relatioinship_type == 'receiver', я($user_id) — получатель дружбы, иначе — отправитель.

Александр Трофимов · 23.09.2013 15:16:04

Следующий шаг — правильные индексы. Здесь не помогу, сам пока-что вникаю. +))

lexikon · 23.09.2013 15:26:50

Понравилась Ваша конструкция....переделал второй запрос на следующий вариант (работает быстрее):

SELECT 

    user_id,

    name,

    surname,

    email,

    patronymic,

    avatar_ext,

    country,

    city,

    position,

    job,

    scope,

    reg_date,

    date as 'req_date',

    status

FROM 

    (SELECT

        `sender_id` as user_id,

        `status`,

        `date`

    FROM

        mw_friend_requests

    WHERE

        receiver_id = $user_id AND `status` = 1

    UNION SELECT

        `receiver_id` as user_id,

        `status`,

        `date`

    FROM

        mw_friend_requests

    WHERE

        `sender_id` = $user_id AND `status` = 1

    ) as fr

LEFT JOIN mw_users u ON u.id = fr.user_id

ORDER BY fr.date DESC

lexikon · 23.09.2013 16:06:54

Посмотрел Ваши запросы выше по поводу второго так полагаю relationship_type это флаг пользователя(ему дали приглашение, или он дал) - думаю очень пригодиться
По поводу первого запроса - ОГРОМНАЯ благодарность работает куда быстрее...

Александр Трофимов · 23.09.2013 19:42:43

lexikon написал:
Посмотрел Ваши запросы выше по поводу второго так полагаю relationship_type это флаг пользователя(ему дали приглашение, или он дал) - думаю очень пригодиться
По поводу первого запроса - ОГРОМНАЯ благодарность работает куда быстрее...

Да, relationship_type помогает понять, кто приглашал.
Пожалуйста. +))

lexikon · 24.09.2013 19:01:23

Возник еще один вопрос Опять касается темы топика...надо извлечь все диалоги с пользователями. Структура таблицы следующая:

CREATE TABLE IF NOT EXISTS `mw_usermessages` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `from` int(10) NOT NULL DEFAULT '0',

  `to` int(10) NOT NULL DEFAULT '0',

  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  `readed` int(11) NOT NULL DEFAULT '0',

  `message` text NOT NULL,

  `deleted` int(2) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  KEY `from` (`from`),

  KEY `to` (`to`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Идеальным решением было бы следующее решение:
- инфомация о сообщении
- информация об отправившем пользователе
- информация об принявшем пользователе
Надо как то извлечь все сообщения в которых участвует непосредственно пользователь(to или from равно $user_id), отсортировать по дате и сгруппировать по полю to или from(зависит от того - чье сообщение было последним), И получить информацию о пользователе чье сообщение последнее....

Если говорить о представлении всего этого, то необходимо реализовать список диалогов, в которых участвовал пользователь, при этом тут же наблюдать чьё сообщение было последним в данном диалоге

наверное ниче не понятно

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

#1 23.09.2013 13:29:54

Запросы JOIN x=3 OR x=2 (оптимизировать)

#2 23.09.2013 13:41:02

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#3 23.09.2013 13:42:17

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#4 23.09.2013 13:48:36

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#5 23.09.2013 13:52:32

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#6 23.09.2013 14:16:06

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#7 23.09.2013 14:22:45

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#8 23.09.2013 14:23:04

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#9 23.09.2013 14:30:16

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

lexikon написал:

#10 23.09.2013 14:34:19

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

Александр Трофимов написал:

#11 23.09.2013 14:41:35

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#12 23.09.2013 14:50:34

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

lexikon написал:

Александр Трофимов написал:

#13 23.09.2013 14:59:02

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#14 23.09.2013 15:14:43

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#15 23.09.2013 15:16:04

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#16 23.09.2013 15:26:50

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#17 23.09.2013 16:06:54

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

#18 23.09.2013 19:42:43

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

lexikon написал:

#19 24.09.2013 19:01:23

Re: Запросы JOIN x=3 OR x=2 (оптимизировать)

Board footer