Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
can you show the EXPLAIN output of the query? –  Derek Downey Feb 22 '11 at 17:11
    
doing the EXPLAIN I think I've found the problem. The main table being returned activity_seismo_info has 382000 rows and I believe it's copying all that data to the tmp table. I thought the LIMIT would have prevented that... –  Patrick Feb 22 '11 at 17:17
    
Interesting. I'm not an expert with the various buffers, but perhaps tuning join_buffer_size variable will help? dev.mysql.com/doc/refman/5.1/en/… –  Derek Downey Feb 22 '11 at 17:57
1  
You'll probably want to ask a new question regarding the separate hard drive for temp tables –  Derek Downey Feb 23 '11 at 15:40
add comment

1 Answer

up vote 2 down vote accepted

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) 
;
share|improve this answer
    
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 –  Patrick Feb 22 '11 at 19:53
    
@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. –  RolandoMySQLDBA Feb 22 '11 at 20:07
1  
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. –  Patrick Feb 22 '11 at 20:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.