Timeline for mysql memory temp table creation SLOW
Current License: CC BY-SA 4.0
32 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 28, 2022 at 0:03 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 28, 2022 at 21:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jan 26, 2022 at 23:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Sep 27, 2021 at 9:06 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 30, 2021 at 2:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jan 28, 2021 at 3:06 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Sep 29, 2020 at 18:04 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 30, 2020 at 1:07 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jan 28, 2020 at 5:02 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Sep 26, 2019 at 23:00 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| May 26, 2019 at 15:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jan 22, 2019 at 7:01 | history | bumped | CommunityBot | This question has answers that may be good or bad; the system has marked it active so that they can be reviewed. | |
| Jun 25, 2018 at 20:54 | comment | added | Josh | SET max_heap_table_size = 134217728000 | |
| Jun 25, 2018 at 20:35 | comment | added | Rick James |
What do you set max_heap_table_size to?
|
|
| Jun 25, 2018 at 20:32 | comment | added | Josh | Ok, for the simple query that fills 4 million rows into a temp table (in < 100ms, underlying query 80 ms) ... the data_length is 100925440 . For the more complex sql that I'm struggling with (15 seconds 4 million rows, < 100 ms underlying query) the resulting table data_length is 467835520 | |
| Jun 25, 2018 at 19:16 | comment | added | Rick James |
SHOW TABLE STATUS; is the simple way.
|
|
| Jun 25, 2018 at 17:36 | comment | added | Josh | The select that is populating the temp table in the create statement runs in < 100ms... that's for the create that takes 15 seconds. On, how big are the tables. I've seen a few different ways to calculate that. How do you do it? | |
| Jun 25, 2018 at 4:52 | comment | added | Rick James | And... How big are the tables? | |
| Jun 25, 2018 at 4:51 | comment | added | Rick James |
Creating the table is (probably) not the costly part -- Doing the SELECT to provide the data is. Run the timings of just the SELECTs, with out the CREATE TEMPORARY TABLE.
|
|
| Jun 25, 2018 at 4:43 | history | edited | Rick James | CC BY-SA 4.0 |
wrap
|
| Jun 25, 2018 at 2:50 | history | edited | Josh | CC BY-SA 4.0 |
added 1697 characters in body
|
| Jun 21, 2018 at 18:27 | comment | added | Josh | yeah, I'll clean up the indexes... that was from doing some experimentation... the 8048999 is the rows scanned. the other number is the serverID or something like that | |
| Jun 21, 2018 at 18:22 | comment | added | Rick James |
Some indexes are redundant. When you have INDEX(a) and INDEX(a,b), drop of the former. Drop joshTest and AssociatedObjectID and AssignmentValue. (This won't solve the problem; it's just a cleanup.)
|
|
| Jun 21, 2018 at 18:15 | comment | added | Rick James | What are the slowlog column headings? In particular, what are 8048999 and 1548456745? I'll guess one is "rows examined". How many rows in each table? | |
| Jun 21, 2018 at 18:14 | history | edited | Rick James | CC BY-SA 4.0 |
wrap some
|
| Jun 21, 2018 at 13:36 | history | edited | Josh | CC BY-SA 4.0 |
added 4754 characters in body
|
| Jun 20, 2018 at 20:43 | answer | added | Rick James | timeline score: 1 | |
| Jun 20, 2018 at 18:49 | comment | added | Josh | I also changed the query so that I was getting back 20K is records. 15 seconds to create... | |
| Jun 20, 2018 at 16:53 | comment | added | Josh | I take back the comment about the super simple select taking the same amount of time. I just ran it again and it took .01 seconds. | |
| Jun 20, 2018 at 16:29 | history | edited | Josh | CC BY-SA 4.0 |
added 3963 characters in body
|
| Jun 20, 2018 at 16:24 | comment | added | Josh | also tried creating in innodb. That was slower... 18 seconds. | |
| Jun 20, 2018 at 15:33 | history | asked | Josh | CC BY-SA 4.0 |