I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website and when I check the
processlist it gets stuck on
copying to tmp table.
I believe the hard drive is just not keeping up, which is causing the hang on the copying to tmp table.
I don't believe I can optimize the query any more efficiently. I was wondering if I could set up my server's second hard drive as the temp table storage so that way I'm spreading out some of the load being put on the server.
The query: SELECT DISTINCT activity_seismo_info.ID, activity_seismo_info.CREATED_AT, activity_seismo_info.UPDATED_AT, .... activity_seismo_info.ASI_EXTRA_5, seismo.ID, .... seismo.SEISMO_NOTES FROM `activity_seismo_info` JOIN activity_info ON (activity_seismo_info.ASI_ACTIVITY_ID=activity_info.ID) JOIN location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID) LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID) WHERE location.ID='193' ORDER BY activity_info.ACTIVITY_EVENT_TIME DESC LIMIT 30
I am on a P4 with 2GB RAM and two hard drives (the second hard drive is not used)
symfony 1.2 application with Propel
thanks to @DTest's suggestion, I found my query was returning all the rows in the table before running a limit on the result. But for future reference, is there a way to setup MySQL to use a second hard drive as the 'scratch' drive for temp tables?