# Query optimization

**URL:** <https://forums.percona.com/t/query-optimization/6582>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [September 18, 2018, 3:32am UTC](https://forums.percona.com/t/query-optimization/6582 "2018-09-18T03:32:22Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 18, 2018, 3:32am UTC](https://forums.percona.com/t/query-optimization/6582/1 "2018-09-18T03:32:22Z")

</div>

I don’t know if this is the right forum to use.  
I’ve moved a Percona 5.7 database from our local premise to a Google Cloud Instance.  
After the move, queries that where executed in 0.000 seconds on our local server, started immediatly to run in 0.030-0.060 seconds.

I have to optimize this. On our local server I used a (huge, 1GB) query cache, but for some mutex contention on GCE, I had to disable it totally. I had tons of query waiting due to caching but disabling it increased a lot the query execution time.

Any idea or suggestion ?

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [September 18, 2018, 3:35am UTC](https://forums.percona.com/t/query-optimization/6582/2 "2018-09-18T03:35:09Z")

</div>

Hi there, you are in the right place!  
I’ll see if the team has any suggestions for you.

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 18, 2018, 3:40am UTC](https://forums.percona.com/t/query-optimization/6582/3 "2018-09-18T03:40:41Z")

</div>

Now i’m trying with query cache enabled and just 100M or query\_cache\_size.  
Our slowest procedure (that makes hundreds of queries) dropped from 2.5 seconds to 1.6-1.7

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 18, 2018, 3:42am UTC](https://forums.percona.com/t/query-optimization/6582/4 "2018-09-18T03:42:09Z")

</div>

Current config:

[mysqld]  
skip-name-resolve  
performance\_schema = OFF

max\_connections = 400

query\_cache\_limit = 1M  
query\_cache\_size = 100M  
query\_cache\_type = 1  
#query\_cache\_size = 0  
#query\_cache\_type = 0

table\_open\_cache = 10240  
tmp\_table\_size = 512M  
max\_heap\_table\_size = 512M

thread\_cache\_size = 128  
thread\_pool\_size = 32

open\_files\_limit = 8192

wait\_timeout = 30  
interactive\_timeout = 30

join\_buffer\_size = 4M

innodb\_file\_per\_table = ON  
innodb\_buffer\_pool\_size = 4G  
innodb\_buffer\_pool\_instances = 4  
innodb\_log\_file\_size = 128M  
innodb\_flush\_log\_at\_trx\_commit = 2  
innodb\_flush\_method = O\_DIRECT  
innodb\_stats\_on\_metadata = OFF  
innodb\_lock\_wait\_timeout = 50

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 20, 2018, 2:23am UTC](https://forums.percona.com/t/query-optimization/6582/5 "2018-09-20T02:23:30Z")

</div>

Any hint? I’m really stuck in trying to optimize this server.

---

<div class="post-metadata">

**Author:** ![Ceri\_Williams](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ceri_williams/32/1218_2.png) [@Ceri\_Williams](https://forums.percona.com/u/Ceri_Williams)\
**Post date:** [September 20, 2018, 5:50am UTC](https://forums.percona.com/t/query-optimization/6582/6 "2018-09-20T05:50:31Z")

</div>

Hi [gandalf](https://www.percona.com/forums/member/20924-gandalf)

The best option is to disable the query\_cache and focus your attention on why the queries are slow; the query cache is disabled by default for a reason, it doesn’t scale (as you experienced) and has in fact been removed from MySQL. You can read more about this on the MySQL Server Team blog ([url][https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/[/url]](https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/%5B/url%5D)).

For example, how much data do you have? There is only a 4G buffer pool and so if you have more than that amount of data then you may be needing to pull from disk. Similarly, if you are executing queries that need to sort on disk then queries once again become slower. The following resources should help you:  
[LIST]  
[_][url][Upcoming Webinars](https://www.percona.com/resources/webinars/troubleshooting-slow-queries%5B/url%5D)  
[_][url][https://www.percona.com/doc/percona-server/5.7/diagnostics/slow\_extended.html[/url]](https://www.percona.com/doc/percona-server/5.7/diagnostics/slow_extended.html%5B/url%5D).  
[/LIST]  
You could also use PMM ([url][Percona Monitoring and Management](https://www.percona.com/doc/percona-monitoring-and-management/index.html%5B/url%5D)) to investigate what is behind the queries being slower than you expect.

Kind regards

Ceri

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 20, 2018, 10:18am UTC](https://forums.percona.com/t/query-optimization/6582/7 "2018-09-20T10:18:09Z")

</div>

The whole db is less than 500mb. Query cache solved a lot of issues, i had trouble with query cache only after moving from our server google Cloud. Keep in mind that i have about 1600-1800 selects per second VS less than 7-10 (ten) writes per second

If could be useful: i don’t have myisam tables so i can reduce as much as possible everything related to it

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 21, 2018, 2:08am UTC](https://forums.percona.com/t/query-optimization/6582/8 "2018-09-21T02:08:31Z")

</div>

I’ve disabled query cache (immediatly execution time increased a lot) and added the following:

# Percona

log\_slow\_filter = full\_scan,full\_join,tmp\_table\_on\_disk,filesort,filesort\_on\_disk  
slow\_query\_log\_file = /var/log/mysql/slow.log  
long\_query\_time = 10  
slow\_query\_log = ON

I don’t see anything logged, thus all executed query are properly using indexes and not making full scans or similiar.

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 21, 2018, 3:57am UTC](https://forums.percona.com/t/query-optimization/6582/9 "2018-09-21T03:57:39Z")

</div>

This is the other query:

select \* from `orders` where `id` IN (  
SELECT `orders_drivers`.`order_id`  
FROM `orders_drivers`  
WHERE `driver_id` = 134  
AND `ignored` = 0  
AND FROM\_UNIXTIME(delivery\_at, GET\_FORMAT(DATE,“ISO”)) = FROM\_UNIXTIME(1537561200, GET\_FORMAT(DATE,“ISO”))  
AND `delivery_at` \> 1537561200  
) order by `delivery_at` asc limit 1;

---

<div class="post-metadata">

**Author:** ![Ceri\_Williams](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ceri_williams/32/1218_2.png) [@Ceri\_Williams](https://forums.percona.com/u/Ceri_Williams)\
**Post date:** [September 21, 2018, 6:51am UTC](https://forums.percona.com/t/query-optimization/6582/10 "2018-09-21T06:51:39Z")

</div>

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

```auto
long_query_time = 10

```

will show you nothing. Try something like the following:

```auto
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

```auto
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.

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

mysql> SELECT &#64;ts BETWEEN &#64;day_start_ts AND &#64;day_end_ts, &#64;ts, &#64;day_start_ts, &#64;day_end_ts;
+-------------------------------------------+------------+---------------+-------------+
| &#64;ts BETWEEN &#64;day_start_ts AND &#64;day_end_ts | &#64;ts | &#64;day_start_ts | &#64;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.:

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

```

Hopefully that will help you find the queries.

Ceri

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 21, 2018, 9:35am UTC](https://forums.percona.com/t/query-optimization/6582/11 "2018-09-21T09:35:29Z")

</div>

Thank you for the reply.

```auto

long_query_time = 10

```

this was a typo, i’ve set that to 0 and log\_slow\_rate\_limit=50 just to increase verbosity.

I’ve replaced the above query with 2 single queries like the following:

```auto

SELECT `orders_drivers`.`order_id` 
FROM `orders_drivers` FORCE INDEX(`driver_id_AND_ignored_AND_delivery_at`)
WHERE `driver_id` = 46 
AND `ignored` = 0 
AND `delivery_at` BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND)
AND `delivery_at` < 1537549200 

ORDER BY `delivery_at` DESC
LIMIT 1

```

and then just a simple

```auto

SELECT * FROM `orders` WHERE `id` = :id

```

:id is the order\_id returned by the previous query Thanks to the index “FORCE INDEX(`driver_id_AND_ignored_AND_delivery_at`)” I was able to remove the filesort:

```auto

+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | orders_drivers | NULL | range | driver_id_AND_ignored_AND_delivery_at | driver_id_AND_ignored_AND_delivery_at | 11 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+

```

This should be faster, right ? The aim is to look for the first delivery immediatly before the provided timestamp. (the where on CURDATE is to avoid searching across the whole table but insted just fetching the current day orders. I don’t know if this is useful in some way)

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 21, 2018, 9:40am UTC](https://forums.percona.com/t/query-optimization/6582/12 "2018-09-21T09:40:49Z")

</div>

Now there are at least 2 more queries to optimize, if possible.  
The biggest one (called tons of times):

```auto

EXPLAIN

select * from `delivery_zones` where ST_Contains(zone, POINT(x,y)) and `restaurant_id` = 100232 and ((`weekdays` & 16 or `weekdays` is null) and ((`hour_starts_at` is null and `hour_ends_at` is null) or (`hour_starts_at` is null and `hour_ends_at` >= '18:50:00') or (`hour_starts_at` <= '18:50:00' and `hour_ends_at` is null) or ('18:50:00' BETWEEN `hour_starts_at` AND `hour_ends_at`)) and ((`starts_at` is null and `ends_at` is null) or (`starts_at` is null and `ends_at` >= '2018-09-21 18:50:00') or (`starts_at` <= '2018-09-21 18:50:00' and `ends_at` is null) or ('2018-09-21 18:50:00' BETWEEN `starts_at` AND `ends_at`))) and `active` = 1 and `delivery_zones`.`deleted_at` is null order by `layer` asc;

+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | delivery_zones | NULL | ref | zone,active,weekdays,restaurant_id,deleted_at,hours,starts_ends | restaurant_id | 5 | const | 1 | 5.00 | Using index condition; Using where; Using filesort |
+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

```

is making a filesort every time due to the ORDER BY `layer`

and

```auto

EXPLAIN 

select * from `products_categories_promotions` where `products_categories_promotions`.`category_id` = 1333 and `products_categories_promotions`.`category_id` is not null and `active` = 1 and (`starts_at` is null or `starts_at` < '2018-09-21 17:08:00') and (`ends_at` is null or `ends_at` > '2018-09-21 17:08:00') and `products_categories_promotions`.`deleted_at` is null order by `created_at` desc;

+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | products_categories_promotions | NULL | ref | FK_products_categories_promotions_products_categories | FK_products_categories_promotions_products_categories | 4 | const | 1 | 5.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.05 sec)

```

that is still making a filesort but is called less time. Is not so bad.

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 25, 2018, 9:58am UTC](https://forums.percona.com/t/query-optimization/6582/13 "2018-09-25T09:58:06Z")

</div>

Any help with my latest two queries ? I’m still trying to optimize this app one query per time

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [September 26, 2018, 10:03am UTC](https://forums.percona.com/t/query-optimization/6582/14 "2018-09-26T10:03:51Z")

</div>

Is safe to replace all occurrencies like the following

```auto

and (
(`starts_at` is null and `ends_at` is null) 
or (`starts_at` is null and `ends_at` >= '2018-09-26 18:30:00') 
or (`starts_at` <= '2018-09-26 18:30:00' and `ends_at` is null) 
or ('2018-09-26 18:30:00' BETWEEN `starts_at` AND `ends_at`)
)

```

to something like this:

```auto

AND (
'2018-09-26 18:30:00' BETWEEN COALESCE(`starts_at`, (NOW() - INTERVAL 1 DAY)) AND COALESCE(`ends_at`,(NOW() + INTERVAL 1 DAY))
)

```

Obviously in the real code ‘2018-09-26 18:30:00’ is the current datetime.  
The aim is to retrieve all records where starts\_at is in the past (or is null) AND ends\_at is in the future (or is null)

In other words, all records that are not explicitly expired (ends\_at in the past) or still to come (starts\_at in the future)

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [October 2, 2018, 8:11am UTC](https://forums.percona.com/t/query-optimization/6582/15 "2018-10-02T08:11:25Z")

</div>

Any help ?

---

<div class="post-metadata">

**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\
**Post date:** [October 2, 2018, 8:36am UTC](https://forums.percona.com/t/query-optimization/6582/16 "2018-10-02T08:36:19Z")

</div>

Hey there, thanks for reaching out. Hopefully someone else from the community might be able to chime in on this for you.

If you have LOTS of queries coming up one at a time, it’s not going to be practical or realistic for the Percona team to be helping you out on a case-by-case basis, as the questions are going to be too specific to your own environment. We’ve given it a shot, but really once it becomes particular to an application we’re at the end of where it’s appropriate to provide professional assistance in an ‘open source and free’ forum.

If you need some professional support, though, I am happy put you in touch with the sales engineers here. They’d be able to look your case and work out how best to help you out.

---

<div class="post-metadata">

**Author:** ![gandalf](https://avatars.discourse-cdn.com/v4/letter/g/59ef9b/32.png) [@gandalf](https://forums.percona.com/u/gandalf)\
**Post date:** [October 7, 2018, 10:43am UTC](https://forums.percona.com/t/query-optimization/6582/17 "2018-10-07T10:43:32Z")

</div>

The whole system is hard to describe here  
what im trying to do is optimizing a couple of query called hundreds time per page access