# Slow  Create Temp Table/ CREATE INDEX on temp table

**URL:** <https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [October 20, 2020, 10:19am UTC](https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159 "2020-10-20T10:19:53Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [October 20, 2020, 10:19am UTC](https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159/1 "2020-10-20T10:19:53Z")

</div>

version | 5.7.26-29

version\_comment | Percona Server (GPL), Release 29, Revision 11ad961

Sometimes creating empty temp tables or adding indexes on emprty temp tables can take seconds rather than milliseconds.

Looking at events\_waits\_current data when the objects are being created the waits are.

**last\_wait: wait/io/table/sql/handler**

**last\_wait: wait/io/file/sql/FRM**

I also found the /mysql/data/ibtmp1 has the highest wait time in the file\_summary\_by\_instance table. The server is an ec2 instance and we don’t appear to getting near the provisioned iops for the MySQL drive.

Sometimes the objects are created fast other times they are really slow. Please can anyone suggest the underlying cause or how I can understand the problem better.

Thanks.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [October 23, 2020, 8:52am UTC](https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159/2 "2020-10-23T08:52:27Z")

</div>

I’m still struggling with this so if anyone can offer any insights it would be great.

When the problem occurs it’s like many statements are stuck. As there are many statements with similar processlist time. This would indicate blocking of some description but I can’t see any contention.

There are no rows in the innodb\_locks or lock\_waits.

The create temporary table statement in question has a processlist\_time of 14 seconds and state: creating table.

extended\_waits\_current has the following values:

last\_statement\_latency: 13.61 s

last\_wait: NULL

last\_wait\_latency: NULL

source: NULL

CPU was 29%

No spike in IPOS or queue.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [October 23, 2020, 9:02am UTC](https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159/3 "2020-10-23T09:02:58Z")

</div>

Just one thing to add. Many of the other statements that are running at the same time have a state: Opening tables.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [October 27, 2020, 8:41am UTC](https://forums.percona.com/t/slow-create-temp-table-create-index-on-temp-table/8159/4 "2020-10-27T08:41:04Z")

</div>

Just wondering if anyone has any suggestions what the bottleneck might be? Thanks.