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 am using mysql 5.5. I create following temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.%s (
        `count` int(10) NOT NULL,
        `m_class` enum('SMS','EMAIL') NOT NULL,
        `priority` enum('HIGH','DEFAULT') NOT NULL,
        `time_taken` bigint(10) NOT NULL,
         KEY `m_class` (`message_class`),
         KEY `priority` (`priority`),
         KEY `time_taken` (`time_taken`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My temporary table (both temp_lat2 and temp_lat3) has around 1 million records. And then I fire following query:

SELECT SUM(t1.count) sr, t2.time_taken l , SUM(t1.count)/<some_integer_value> p  
        FROM (SELECT * FROM tempdb.temp_lat2 where m_class = 'SMS' and priority = 'HIGH') t1  
        JOIN (SELECT * FROM tempdb.temp_lat3 where m_class = 'SMS' and priority = 'HIGH') t2 
        ON t1.time_taken < t2.time_taken  
        GROUP BY l  
        HAVING p > 0.99 
        ORDER BY p  
        LIMIT 1;

Now the above query is first for around 10 times via a script which just changes some_integer_value. After 4th or 5th round, the above query gets hung and that too for more than 2 to 3 hrs, and at the end I have to kill the query.

Can anybody suggest the reason behind this problem? Is something wrong with my query?

share|improve this question
    
I would try to remove the derived tables from the query and write it with a simple FROM tempdb.temp_lat2 AS t1 JOIN tempdb.temp_lat3 AS t3 ON t1.time_taken < t2.time_taken WHERE t1.m_class = 'SMS' AND t1.priority = 'HIGH' ... –  ypercube Jan 25 at 22:01
    
And add a composite index on (m_class, priority, time_taken), on each one of the tables. –  ypercube Jan 25 at 22:02
    
@ypercube: Thanks for your comment. Can you explain the reason behind composite key that you have suggested? I totally agree with what you said about avoiding derived tables (ref link: mysqlperformanceblog.com/2006/08/31/…) –  Arry Jan 26 at 5:46

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.