Mysql Оптимизация запроса с group by по полю из джойна

Фанат

oncle terrible
Команда форума
Запрос простой
Код:
SELECT t.device, COUNT(1) FROM `order` o
INNER JOIN traffic t ON o.id = t.order_id AND  t.sort = 1
WHERE o.order_date BETWEEN '2018-02-16 00:00:00' AND  '2018-02-22 23:59:59'
GROUP BY t.device
Код:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
         type: range
possible_keys: PRIMARY,order_date_idx,
          key: order_date_idx
      key_len: 5
          ref: NULL
         rows: 520748
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ref
possible_keys: IDX_65E3C3DF8D9F6D38
          key: IDX_65E3C3DF8D9F6D38
      key_len: 5
          ref: o.id
         rows: 1
        Extra: Using where

Код:
{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "temporary_table": {
        "function": "buffer",
        "table": {
          "table_name": "o",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "order_date_idx",
          ],
          "key": "order_date_idx",
          "key_length": "5",
          "used_key_parts": ["order_date"],
          "rows": 520748,
          "filtered": 100,
          "attached_condition": "(o.order_date between '2018-02-16 00:00:00' and '2018-02-22 23:59:59')",
          "using_index": true
        },
        "table": {
          "table_name": "t",
          "access_type": "ref",
          "possible_keys": [
            "IDX_65E3C3DF8D9F6D38",
          ],
          "key": "IDX_65E3C3DF8D9F6D38",
          "key_length": "5",
          "used_key_parts": ["order_id"],
          "ref": ["o.id"],
          "rows": 1,
          "filtered": 100,
          "attached_condition": "(t.sort = 1)"
        }
      }
    }
  }
}

Собственно, вопрос - возможно ли в принципе здесь убрать временную таблицу? Без денормализации, понятное дело.
Дальше индекса по [order_date, id] (который не работает и в осмысленности которого я сомневаюсь) у меня мысль не идет.

10.1.26-MariaDB if that matters.
 

Фанат

oncle terrible
Команда форума
Ну, вообще-то там доктринные индексы, дым коромыслом. В одном 18, во втором 33. Что, кстати, тоже отдельная тема, но не сейчас.
Я специально не хотел показывать create table / show index, меня общее решение интересует.
Ты кардинальность хотел посмотреть?

В принципе, если очевидного решения нет, я конечно подорвусь сделать искусственный тест кейс, и приведу его тогда целиком - и с индексами, и с таблицами, и с данными.
 

Фанат

oncle terrible
Команда форума
Меня чуток смущает

Может его вынести наружу?
Ну вообще он и был снаружи, я его в самом начале опытов внёс.

Но вообще мне кажется, что он-то не влияет. Если убрать это условие вообще, то строк больше находит, но выборка из ордерс остается такой же печальной
 

Фанат

oncle terrible
Команда форума
Ну, собственно, вот
PHP:
#$pdo->query("set global innodb_flush_log_at_trx_commit = 2");
$pdo->query("create table ycs_sales (id int auto_increment primary key, dtm datetime)");
$stmt = $pdo->prepare("insert into ycs_sales values (null, ?)");
foreach (range(mktime(0,0,0,2,1,2018), mktime(0,0,0,2,28,2018)) as $stamp){
    $stmt->execute([date("Y-m-d", $stamp)]);
}
$max_id = $pdo->lastInsertId();
$pdo->query("alter table ycs_sales add key(dtm)");

$pdo->query("create table ycs_products (id int auto_increment primary key, sales_id int, name varchar(255))");
$stmt = $pdo->prepare("insert into ycs_products values (null, ?, ?)");
$products = ['food', 'drink', 'vape'];
foreach (range(1, $max_id) as $id){
    $stmt->execute([$id, $products[rand(0,2)]]);
}
$pdo->query("alter table ycs_products add key(sales_id)");
Код:
SELECT p.name, COUNT(1) FROM ycs_sales s
INNER JOIN ycs_products p ON s.id = p.sales_id 
WHERE s.dtm BETWEEN '2018-02-16 00:00:00' AND  '2018-02-22 23:59:59'
GROUP BY p.name
 

Фанат

oncle terrible
Команда форума
Ага, спасибо.
Это интересный трюк, я не знал, как избавиться от эксплицитной сортировки при груп бай.

Но общую картину не меняет, из за временной таблицы время запроса как было 2 секунды, так и осталось :(
 

WMix

герр M:)ller
Партнер клуба
Код:
create table product_names
select distinct name from ycs_products;

ALTER TABLE `product_names` ADD PRIMARY KEY( `name`);
ALTER TABLE `ycs_products` ADD INDEX(`name`);

select name, (
    SELECT COUNT(1)
    FROM ycs_sales s
    JOIN ycs_products p ON s.id = p.sales_id
    WHERE s.dtm BETWEEN '2018-02-16 00:00:00' AND  '2018-02-22 23:59:59' and p.name = pn.name
)
from product_names pn
Код:
mysql> explain select name, (   SELECT COUNT(*)    FROM ycs_sales s   JOIN ycs_products p ON s.id = p.sales_id    WHERE p.name = pn.name and s.dtm BETWEEN '2018-02-10 00:00:00' AND  '2018-02-12 23:59:59'  ) from product_names pn;
+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref             | rows  | filtered | Extra       |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
|  1 | PRIMARY            | pn    | NULL       | index  | NULL          | PRIMARY | 767     | NULL            |     2 |   100.00 | Using index |
|  2 | DEPENDENT SUBQUERY | p     | NULL       | ref    | sales_id,name | name    | 768     | test.pn.name    | 86478 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | s     | NULL       | eq_ref | PRIMARY,dtm   | PRIMARY | 4       | test.p.sales_id |     1 |    50.00 | Using where |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------------+-------+----------+-------------+
3 rows in set, 2 warnings (0,00 sec)
я твои тестданные подрезал, у меня цифры другие
 

Фанат

oncle terrible
Команда форума
Хм. Эксплейн у меня такой же,
Код:
+------+--------------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
| id   | select_type        | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra       |
+------+--------------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
|    1 | PRIMARY            | pn    | index  | NULL          | PRIMARY | 257     | NULL            |      3 | Using index |
|    2 | DEPENDENT SUBQUERY | p     | ref    | sales_id,name | name    | 258     | test.pn.name    | 582233 | Using where |
|    2 | DEPENDENT SUBQUERY | s     | eq_ref | PRIMARY,dtm   | PRIMARY | 4       | test.p.sales_id |      1 | Using where |
+------+--------------------+-------+--------+---------------+---------+---------+-----------------+--------+-------------+
а время выросло с двух секунд до двух с половиной минут...
 

WMix

герр M:)ller
Партнер клуба
а время выросло с двух секунд до двух с половиной минут...
лень твои данные загонять, но на твой запрос у меня 3 rows in set (0,50 sec), а на мой 3 rows in set (0,28 sec)
А она не выдаст рандомный name-count ?
там всего 1 запись COUNT(1) where p.name = pn.name
 

AnrDaemon

Продвинутый новичок
COUNT(1) == COUNT(0) == COUNT(*)
на сколько я помню.
А при том, что этот COUNT стоит внутри подзапроса, результаты внешнего запроса стремительно мигрируют в область мистики.
 

WMix

герр M:)ller
Партнер клуба
этот COUNT стоит внутри подзапроса, результаты внешнего запроса стремительно мигрируют в область мистики.
внешний запрос выводит 3 записи, на каждую из которых запускается подзапрос с count на каждую из записей, нет?
 

Фанат

oncle terrible
Команда форума
лень твои данные загонять
Если у тебя неполные данные, то запрос может по-другому строиться.

Вообще, я так понимаю что твой вариант навроде того что мне посоветовали на стаковерфлое: он выбирает все строки из продуктс, ищет им соответствие в сейлзе и только потом отбрасывает лишнее.
Этот вариант гарантированно не подойдет, нам надо плясать от заказов, ограниченных датами.
 
Сверху