Timeline for MySQL INNODB SELECT query hanging on copying to tmp table
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| S Oct 15, 2012 at 14:44 | history | suggested | Serge S. | CC BY-SA 3.0 |
improved formatting
|
| Oct 15, 2012 at 14:41 | review | Suggested edits | |||
| S Oct 15, 2012 at 14:44 | |||||
| Mar 17, 2011 at 21:09 | vote | accept | Patrick | ||
| Feb 22, 2011 at 20:32 | comment | added | Patrick |
well, it did knock the massive row result to about 40% of the original(382000 -> 150400), but I think the way to truly cut the return down to an appropriate size would be to run a subselect ala SELECT seismo.id FROM seismo WHERE seismo.seismo_location_id = '193';. Only problem, I've never done much with subselects and can't seem to get them to work.
|
|
| Feb 22, 2011 at 20:07 | comment | added | RolandoMySQLDBA | @Patrick I used your original query but slid the "location.ID='193'" clause into the location alias. Please see if that changes the EXPLAIN plan. | |
| Feb 22, 2011 at 20:06 | history | edited | RolandoMySQLDBA | CC BY-SA 2.5 |
added 68 characters in body; edited body
|
| Feb 22, 2011 at 19:53 | comment | added | Patrick |
unfortunately the way the database is designed, the activity_seismo_info table is not tied to location, it is tied to the seismo table which is tied to the location. I am looking at denormalizing to an extent the next iteration of the database to remove the circuitous queries I sometimes have to make
|
|
| Feb 22, 2011 at 19:38 | history | edited | RolandoMySQLDBA | CC BY-SA 2.5 |
added 635 characters in body
|
| Feb 22, 2011 at 19:32 | history | answered | RolandoMySQLDBA | CC BY-SA 2.5 |