Уменьшить нагрузку на запрос

182

15 декабря 2016, 15:58

Есть база данных сообщений со структурой:

id       - ID
user_id  - ID отправившего сообщение
owner_id - ID получившего сообщение
text     - текст сообщения
created  - дата создания в формате Y-m-d H:i:s

Данные в базе:

id | user_id | owner_id | text               | created
-------------------------------------------------------------------
1  |    1    |     2    | Привет Коля        | 2000-01-01 00:00:01
2  |    2    |     1    | Здорова Кирилл     | 2000-01-01 00:00:02
3  |    2    |     1    | Как дела?          | 2000-01-01 00:00:03
4  |    1    |     3    | Привет Серега!     | 2000-01-01 00:00:04
5  |    3    |     1    | ООО Кирюха привет! | 2000-01-01 00:00:05
6  |    2    |     3    | От Коли - Сергею   | 2000-01-01 00:00:06
7  |    3    |     1    | Есть денег в долг? | 2000-01-01 00:00:07
8  |    1    |     4    | Ну что у нас?      | 2000-01-01 00:00:08

Имеется следующий запрос:

'SELECT
    id, user_id, owner_id, text, created, (user_id + owner_id) uid
 FROM
    (SELECT *
     FROM messages
     ORDER BY created DESC) AS msg
 WHERE
    user_id = :user_id OR owner_id = :user_id
 GROUP BY uid
 ORDER BY created DESC
 LIMIT :limit'

При выборке хочу получить вот такой результат:

id | user_id | owner_id | uid | text               | created
-------------------------------------------------------------------
8  |    1    |     4    |  5  | Ну что у нас?      | 2000-01-01 00:00:08
7  |    3    |     1    |  4  | Есть денег в долг? | 2000-01-01 00:00:07
3  |    2    |     1    |  3  | Как дела?          | 2000-01-01 00:00:03

Как не выбирать под-запросом целую таблицу ? там же может быть до 1 000 000 записей!

Нужна группировка по максимальной дате

Answer 1

Примерно так:

SELECT
   id, user_id, owner_id, text, created
FROM messages
WHERE
    user_id = 1
GROUP BY owner_id
UNION ALL
SELECT
   id, user_id, owner_id, text, created
FROM messages
WHERE
    owner_id = 1
GROUP BY user_id
ORDER BY created DESC
LIMIT 10

При этом должны быть индексы по

user_id
owner_id

на больших объемах выборки будет эффективны составные индексы:

user_id,created
owner_id,created

ЗЫ для других субд, запрос будет сложнее, т.к. в группировку должны входить и другие поля (по которым нет агрегатной функции), а поля с бесконечной длиной типа text, входить в группу не могут, их придется обрезать, приводить к varchar(n)...

UPD Фидл Не очень красиво получается, конечно... Вместо id можно использовать как ключ created, если будет гарантия уникальности дат...