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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using mysql 5.5. I create following temporary table:

        `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`)

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 '14 at 22:01
And add a composite index on (m_class, priority, time_taken), on each one of the tables. – ypercubeᵀᴹ Jan 25 '14 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:…) – Arry Jan 26 '14 at 5:46

Your Answer


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

Browse other questions tagged or ask your own question.