Как эффективно группировать по двум столбцам и возвращать порядок первой строки каждой группы по time_create?

У меня есть большая таблица, которая использовалась для записи количества запасов, она уже заказана time_create.

Теперь я хочу получить последнюю информацию о количестве товаров на складе в каждом магазине.

Код:

select SQ.store_id,SQ.product_id, max(SQ.time_create) as last_time, SQ.store_product_quantity_new  
from stockquantitylogs as SQ 
where SQ.time_create > unix_timestamp("2018-08-01")*1000  
group by SQ.store_id, SQ.product_id 

Но это очень-очень медленно.

Поэтому мне интересно, как сделать мой запрос более эффективным.

PS: SQ.store_product_quantity_new достается от max(SQ.time_create)? Не уверен в этом, просто хочу получить store_product_quantity_new с max(SQ.time_create)


Состав

create table stockquantitylogs
(
  id                         char(36) collate utf8_bin default '' not null
    primary key,
  store_id                   char(36) collate utf8_bin default '' not null,
  product_id                 char(36) collate utf8_bin default '' not null,
  time_create                bigint                               not null,
  time_update                bigint                               not null,
  store_product_quantity_old int                                  not null,
  store_product_quantity_new int                                  not null
);

create index IX_product_id
  on stockquantitylogs (product_id);

create index IX_store_id
  on stockquantitylogs (store_id);

create index IX_time_create
  on stockquantitylogs (time_create);

create index IX_time_update
  on stockquantitylogs (time_update);

Структура таблицы (с деталями индексации) с результатами оператора EXPLAIN будет полезна

Madhur Bhaiya 07.09.2018 08:29

Таблицы - это unordered sets, ТОЛЬКО при запросе на order by вы можете гарантировать, что он будет представлен в ожидаемом порядке.

Paul Maxwell 07.09.2018 08:34

Думаю, было бы полезнее: meta.stackoverflow.com/questions/333952/…

Strawberry 07.09.2018 08:34

Какую версию MySQL вы используете?

Deebster 07.09.2018 08:36

@Deebster 5.6 Думаю

Mithril 07.09.2018 08:47

@Mithril, пожалуйста, опубликуйте также результаты Explain Statement. Кроме того, учитывая, что первичным ключом является Char, а store_id также является Char (вместо Int), они определенно будут иметь более низкую производительность по сравнению с использованием Int.

Madhur Bhaiya 07.09.2018 08:56

Следуйте за тегом, который я добавил.

Rick James 21.09.2018 04:29
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
7
37
1

Ответы 1

Вы не можете просто использовать max () для столбца и автоматически выбирать другие столбцы из той же строки. В более поздних версиях MySQL опция ONLY_FULL_GROUP_BY включена по умолчанию, что означает, что даже попытка выполнения этого запроса является ошибкой (см. https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html).

Вам нужно выяснить, какие строки вам нужны, а затем выбрать их:

SELECT sq.store_id, sq.product_id, sq.time_create AS last_time, sq.store_product_quantity_new FROM stockquantitylogs AS sq INNER JOIN ( SELECT store_id, product_id, MAX(time_create) AS time_create FROM stockquantitylogs GROUP BY store_id, product_id ) AS sq_latest USING(store_id, product_id, time_create)

Здесь мы сначала выбираем желаемые (последние) строки с помощью:

SELECT store_id, product_id, MAX(time_create) AS time_create FROM stockquantitylogs GROUP BY store_id, product_id

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

Чтобы это работало хорошо, вам понадобится индекс для соответствующих строк:

INDEX `store_id_product_id_time_create` (`store_id`, `product_id`, `time_create`)

Обратите внимание, что я удалил ваш оператор where - если это необходимо, добавьте его во внутренний запрос sq_latest.

Другие вопросы по теме