Query optimization

MySQL & MariaDB Percona Server for MySQL 5.7
10

Hi, judging by the query response time that you had concerns about, setting

long_query_time = 10

will show you nothing. Try something like the following:

long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1

For the query that you have posted here

FROM_UNIXTIME(delivery_at, GET_FORMAT(DATE,"ISO")) = FROM_UNIXTIME(1537561200, GET_FORMAT(DATE,"ISO"))

If this is aiming to look for an order that is for delivery on the current day then you would be better to check that delivery_at is within a range with constants, e.g.

mysql> SET @ts := UNIX_TIMESTAMP(), @day_start_ts := UNIX_TIMESTAMP(CURDATE()), @day_end_ts := UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @ts BETWEEN @day_start_ts AND @day_end_ts, @ts, @day_start_ts, @day_end_ts;
+-------------------------------------------+------------+---------------+-------------+
| @ts BETWEEN @day_start_ts AND @day_end_ts | @ts | @day_start_ts | @day_end_ts |
+-------------------------------------------+------------+---------------+-------------+
| 1 | 1537532265 | 1537484400 | 1537570799 |
+-------------------------------------------+------------+---------------+-------------+
1 row in set (0.00 sec)

The use of variables here is just to demonstrate, but you would use delivery_at and could just use the functions in the comparison, i.e.:

delivery_at BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND)

Hopefully that will help you find the queries.

Ceri