Оптимизация действительно длинного запроса?

Question

[Юрий Попов]

Приветствую, гуру оптимизации.

Суть следующая:

На сайте есть система диалогов. Есть диалоги между пользователями (from>0, to>0). Есть система групповых диалогов (from>0, to<0). Структура таблиц:

CREATE TABLE `dialogs` (
  `id` int(25) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `title` varchar(512) COLLATE utf8_unicode_ci NOT NULL, # Заполнено только у владельца
  `owner` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', # Показатель, владелец ли данного диалога
  `deleted` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', # Исключён ли данный пользователь из диалога, если исключён владелец - диалог удалён у всех
  PRIMARY KEY (`id`,`user`),
  KEY `user` (`user`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `messages` (
  `from` int(15) NOT NULL, # Отправитель
  `to` int(25) NOT NULL, # >0 - персональное, <0 - групповое
  `text` text COLLATE utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL,
  `deleted_from` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', # Удалено ли сообщение отправителем (в групповом диалоге - у всех)
  `deleted_to` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', # Удалено ли сообщение получателем, в групповом диалоге не используется
  `unread` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1', # Флаг "нового" сообщения
  PRIMARY KEY (`from`,`to`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Задача — получить количество непрочитанных сообщений для текущего пользователя (для вывода в строке меню «новые сообщения (N)»), без учёта отправленных им же, и удалённых.


Мой воспалённый мозг родил следующее:

Database::query($new_messages_statement,array('u'=>User::$self['id']));

Statement:

select count(*) as count from `messages` where # Получаем количество
	`unread`='1' and # Непрочитанные
	`from`!=:u and # Не от самого себя
	(
		(
			`to`=:u and # Написанные мне
			`deleted_to`='0' # И не удалённые мной
		) or ( # Или...
			`deleted_from`='0' and # Которые не удалены владельцем диалога
			`to` in # В диалогах...
				(
					select -(`id`) from `dialogs` where # Получаем список диалогов
						`deleted`='0' and # Не удалённых
						`owner`='1' and # Основная запись
						`id` in
							(
								select `id` from `dialogs` where # Диалоги
								`user`=:u and # В которых я включен
								`deleted`='0' # И из которых меня не удалили
							)
				)
		)
	)

Оно работает так, как надо, но, похоже, что я делаю что-то не совсем правильно…

Наставьте меня на путь истинный, о гуру SQL! :)

Answer 1

[AlexeyVD]

Поправьте меня, если я ошибаюсь, но разве данный запрос:

                    select -(`id`) from `dialogs` where # Получаем список диалогов
                        `deleted`='0' and # Не удалённых
                        `owner`='1' and # Основная запись
                        `id` in
                            (
                                select `id` from `dialogs` where # Диалоги
                                `user`=:u and # В которых я включен
                                `deleted`='0' # И из которых меня не удалили
                            )

не аналогичен такому:

                    select -(`id`) from `dialogs` where # Получаем список диалогов
                        `deleted`='0' and # Не удалённых
                        `owner`='1' and # Основная запись
                        `user`=:u  # В которых я включен

Вообще я бы переписал ваш вопрос как-то так (код примерный, у себя не тестировал):

SELECT COUNT(1)
  FROM
    (
      SELECT 1
        FROM messages m1
        WHERE m.to = :u
          AND m.deleted_to = '0'
          AND m1.unread = '1'
          AND m1.from != :u
      UNION ALL
      SELECT 1
        FROM messages m2
        JOIN dialogs d1 ON m.to = -(d1.id)
        WHERE d1.deleted = '0' 
          AND d1.owner = '1'
          AND d1.user = :u
          AND m2.unread = '1'
          AND m2.from != :u
    ) sel

Ну и не забывайте про соответствующие индексы.

Comments

[Юрий Попов]

Суть в том, что для каждого диалога существует N записей:

ID	USER	TITLE	OWNER	DELETED
1	1	...	1	0		# основная запись - создатель диалога
1	2		0	0		# ещё один участник диалога
1	3		0	1		# а этого исключили
2	3	...	1	1		# а этот диалог вообще удалён
2	1		0	0		# но юзера из него не исключили, однако,
						  диалог ему больше видеть не нужно

[AlexeyVD]

Тогда вот так:

SELECT COUNT(1)
  FROM
    (
      SELECT 1
        FROM messages m1
        WHERE m.to = :u
          AND m.deleted_to = '0'
          AND m1.unread = '1'
          AND m1.from != :u
      UNION ALL
      SELECT 1
        FROM messages m2
        JOIN dialogs d1 ON m.to = -(d1.id)
        JOIN dialog d2 ON d2.id = d1.id
        WHERE d1.deleted = '0' 
          AND d1.owner = '1'
          AND d2.user = :u
          AND d2.deleted = '0'
          AND m2.unread = '1'
          AND m2.from != :u
    ) sel

[Юрий Попов]

Интересно… Надо будет как-нибудь на досуге почитать мануалы по этим JOIN-ам и UNION-ам. Спасибо.

[Юрий Попов]

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

SELECT COUNT(1) as count
  FROM
    (
      SELECT 1
        FROM messages m1
        WHERE m1.to = :u
          AND m1.deleted_to = '0'
          AND m1.unread = '1'
          AND m1.from != :u
      UNION ALL
      SELECT 1
        FROM messages m2
        JOIN dialogs d1 ON m2.to = -(d1.id)
        JOIN dialogs d2 ON d2.id = d1.id
        WHERE d1.deleted = '0' 
          AND d1.owner = '1'
          AND d2.user = :u
          AND d2.deleted = '0'
          AND m2.unread = '1'
          AND m2.deleted_from = '0'
          AND m2.from != :u
    ) sel

Answer 2

[xmoonlight]

Гораздо проще все…

Проверки (WHERE):
1. Сообщение адресовано мне: `to`=`username`
2. Оно не прочитано (я бы сделал обратную переменную или по-умолчанию знач. 1): `unread`=1

Остальных вариантов быть не должно!
При любом действии с сообщением со стороны пользователя — оно должно быть прочитано (флаг должен ставиться автоматом).
Если Вы не в этом диалоге — Вы не можете отправить сообщение в этот диалог (с его ID): проверяется при добавлении(создании) нового сообщения в БД.
Кто инициатор конфы/комнаты и кто удалил/не удалил сообщение(-ия)/пользователя(-ей) — абсолютно не важно.
Если хотите еще быстрее: добавьте в конце LIMIT 11 и проверьте что если COUNT>10, то выведите «У Вас больше 10 непрочитанных сообщений!».

Comments

[Юрий Попов]

Всё несколько драматичнее.
Во-первых, если один пользователь удалил у себя сообщение, то у второго оно так или иначе должно остаться.
Во-вторых, в групповых диалогах, должна быть возможность как исключения пользователей, так и удаления диалога. Но сообщениям должен просто ставиться флаг «удалён», а в базе оно должно оставаться (и не спрашивайте, почему).

[Юрий Попов]

Оно не прочитано (я бы сделал обратную переменную или по-умолчанию знач. 1): `unread`=1

Именно так и сделано:

`unread` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1'