Разбить записи на периоды

[Q_BASIC]

Приветствую,

Есть таблица со столбцами id и time. time типа INT(11), где хранится unix time. Надо для графика разбить записи по дням(за последние 30 дней) и по месяцам (последние 12). Сколько было записей вчера, сколько позавчера...

Как это лучше сделать?

Что-то типа такого надо: Для просмотра ссылки Войди или Зарегистрируйся (js сам сделаю, надо записи получить)

 

[alex_me]

так ?

WHERE DATE(my_date) <= CURDATE() + 30
GROUP BY DATE(my_date)

ps
или так возможно

GROUP BY DATE(FROM_UNIXTIME(my_date))

 

[Q_BASIC]

так ?

WHERE DATE(my_date) <= CURDATE() + 30
GROUP BY DATE(my_date)

ps
или так возможно

GROUP BY DATE(FROM_UNIXTIME(my_date))

SELECT COUNT(*) as `count`, FROM_UNIXTIME(`created`, '%e.%c.%y') as `date` FROM `accounts` WHERE `created` > 1 GROUP BY `date`

В where через php поставлю time()-2592000. Запрос нормальный, не тяжелый будет?

 

[Black Hat]

В where через php поставлю time()-2592000. Запрос нормальный, не тяжелый будет?

А сами как думаете?

mysql> explain extended SELECT COUNT(*) as `count`, FROM_UNIXTIME(`created`, '%e.%c.%y') as `date` FROM `accounts` WHERE `created` > 1 GROUP BY `date`;
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | accounts | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+

Тут полный набор всего того плохого, что может случиться.

1. нет индекса (так как вы не предоставили DDL, полагаю, что так и есть)
2. WHERE `created` > 1 - это значит полный проход таблицы (полагаю что записей с датой 0 и 1 нет или мало)
3. над каждой строкой будет выполнена функция FROM_UNIXTIME (даже если бы были индексы, они не помогут)
4. используется временная таблица
5. эта временная таблица сортируется, снова без индексов

как я поступаю в таких случаях:

1. какого размера таблица? несколько тысяч - сотен тысяч? - забейте на оптимизацию, такое количество строк мускул пережует не подавится
2. как можно использовать индекс? два способа
- денормализация, добавим еще одну колонку, где уже будет значение типа DATE, добавить индекс по нему, актуальность можно поддерживать триггерами, выборка по такому столбцу будет быстрой. если интересно, покажу как сделать.
- переделать запрос, он будет монстром, но работать довольно быстро:

1) делаем индекс на `created`
2) вот такой будет запрос для "Надо для графика разбить записи по дням(за последние 30 дней)"

(SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
UNION ALL
(SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999)
... продолжаем для остальных дней

Вот так выглядит до создания индекса:

mysql> explain extended
    -> (SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
    -> UNION ALL
    -> (SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999);
+----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY      | accounts   | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
|  2 | UNION        | accounts   | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL |             |
+----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+

Уже нет временной таблицы и сортировки.

Добавим индекс:

ALTER TABLE `accounts` ADD KEY(`created`);
mysql> explain extended
    -> (SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
    -> UNION ALL
    -> (SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999);
+----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY      | accounts   | index | created       | created | 4       | NULL |    2 |    50.00 | Using where; Using index |
|  2 | UNION        | accounts   | index | created       | created | 4       | NULL |    2 |    50.00 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL |                          |
+----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+

Уже используется индекс.