MySQL INNODB SELECT query hanging on copying to tmp table

Question

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?

Answer 1

In my.cnf use this option

tmpdir=/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 :

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 (SELECT ID from location WHERE ID='193') location ON (activity_info.ACTIVITY_LOCATION_ID=location.ID)
    LEFT JOIN seismo ON (activity_seismo_info.ASI_SEISMO_ID=seismo.ID) 
;