Optimizing a really long query?

Question

MySQL

Hello optimization gurus.
The essence of the following:
The site has a system of dialogues. There are dialogs between users (from>0, to>0). There is a system of group dialogues (from>0, to<0). Table structure:

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;

The task is to get the number of unread messages for the current user (to be displayed in the menu bar "new messages (N)"), without taking into account those sent by him, and deleted ones.
My inflamed brain gave birth to the following:

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' # И из которых меня не удалили
              )
        )
    )
  )

It works as it should, but it looks like I'm doing something wrong...
Put me on the right track, oh SQL gurus! :)

Answer 1

Correct me if I'm wrong, but is this query:

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

not similar to this:

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

In general, I would rewrite your question somehow like this (the code is approximate, I did not test it myself):

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

Well, do not forget about the corresponding indexes.

Answer 2

Everything is much simpler...
Checks (WHERE):
1. The message is addressed to me: `to`=`username` 2. It is not read (I would make
a reverse variable or default value 1): `unread`=1
shouldn't!
For any action on the message by the user, it must be read (the flag must be set automatically).
If you are not in this dialog, you cannot send a message to this dialog (with its ID): checked when adding (creating) a new message to the database.
Who is the initiator of the conference/room and who deleted/did not delete the message(s)/user(s) is absolutely not important.
If you want even faster: add LIMIT 11 at the end and check that if COUNT>10, then print "You have more than 10 unread messages!".