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_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?