MySQL INNODB SELECT query hanging on copying to tmp table
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) 
;