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)
PHP5.2
MySQL 5.0.51a-3ubuntu5.4
symfony 1.2 application with Propel
UPDATE
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?
Best Answer
In
my.cnf
use this optiontmpdir=/tmp
(this is the default anyway, just choose another folder)Restart of mysql required
BTW since the
location.ID
is'193'
why even involve the location table ???Try the following :