Mysql PHP частная электронная почта, как система обмена сообщениями

Question

Mysql PHP частная электронная почта, как система обмена сообщениями

Я строю небольшую систему обмена сообщениями для своего приложения, основная идея — иметь 2 таблицы.
Table1 сообщения

Id,sender_id,title,body,file,parent_id

Здесь хранятся сообщения, отделенные от того, кто их получит, чтобы разрешить нескольким получателям.

Родитель, я бы связался с родительским сообщением, если оно является ответом, а файл является блобом для хранения одного файла, прикрепленного к сообщению.

Таблица 2 message_users

Id,thread_id,user_id,is_read,stared,deleted

Связать родительский поток с целевыми пользователями,

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

Select count(*) from message_users where user_id = 1 and is_read is null

Чтобы подсчитать все сообщения в своем почтовом ящике я могу сделать

Select count(*) from message_users where user_id = 1;

Вопрос в том, как объединить оба в одном оптимизированном запросе?

0

database-connection messaging mysql php

Решение

Другие решения

В MySQL при подсчете поля он учитывает только ненулевые вхождения этого поля, поэтому вы должны иметь возможность сделать что-то вроде этого:

SELECT COUNT(user_id), COUNT(user_id) - COUNT(is_read) AS unread
FROM message_users
WHERE user_id = 1;

Не проверено, но оно должно указывать вам в правильном направлении.

1

Вы можете использовать сумму с CASE WHEN пункт. Если is_read тогда ноль +1 добавляется к сумме, иначе +0,

SELECT count(*),
SUM(CASE WHEN is_read IS NULL THEN 1 ELSE 0 END) AS count_unread
FROM message_users WHERE user_id = 1;

1

Источник

Accepted Answer

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

|---------|---------|
| total   | unread  |
|---------|---------|
| 20      | 12      |
|---------|---------|

Как таковой понадобится что-то с формой в соответствии с:

SELECT A total, B unread FROM message_users WHERE user_id=1

A довольно просто, у вас уже более или менее есть это: COUNT(Id),

B немного сложнее и может принять форму SUM( IF(is_read IS NULL,1,0) ) — добавьте 1 каждый раз, когда is_read не равен нулю; условие будет зависеть от вашей базы данных.

Или же B может выглядеть так: COUNT(CASE WHEN is_read IS NULL THEN 1 END) unread — это говорит «когда is_read равен нулю, считай еще 1»; условие будет зависеть от вашей базы данных.

В целом:

SELECT COUNT(Id) total, COUNT(CASE WHEN is_read IS NULL THEN 1 END) unread FROM message_users WHERE user_id=1

Или же:

SELECT COUNT(Id) total, SUM( IF(is_read IS NULL,1,0) ) unread FROM message_users WHERE user_id=1

С точки зрения оптимизации, я не знаю о запросе, который обязательно может быть выполнен быстрее, чем этот. (Хотелось бы знать об этом, если он существует!) Могут быть способы ускорить процесс, если у вас есть проблемы с производительностью:

Изучите ваши индексы: используйте встроенные инструменты EXPLAIN и некоторое чтение вокруг и т. д.
Используйте кэши и / или предварительно вычислите значение и сохраните его в другом месте — например, есть поле unread_messages против user и получить это значение напрямую. Очевидно, что потребуется некоторая недействительность при записи или действительно какая-то служба, работающая для поддержания этих значений в актуальном состоянии. Есть много способов достичь этого, инструменты в MySQL, собственные руки и т. Д. И т. Д.
Вкратце, начните оптимизацию с требований и реальных данных. (Мой запрос занимает 0,8 с, мне нужны результаты в 0,1 с, и они должны быть согласованными 100% времени — как мне этого добиться?) Затем вы можете настроить и поэкспериментировать с SQL, оборудованием, на котором работает сервер (возможно?), Кэшированием / предварительным вычислением в разных точках и т. Д.

2