大家帮忙看看这个sql能否优化一下
SELECT * FROM
(
SELECT threads._id, threads_tmp._id AS smsid, threads_tmp.type, threads.read, threads_tmp.msgtype, threads_tmp.status, threads.has_attachment, threads_tmp.msg_read, threads.date, threads.recipient_ids, threads.snippet
FROM threads left JOIN
(
SELECT thread_id, type, _id, read AS msg_read, msgtype, status
FROM
(
SELECT thread_id AS tid, date* 1000 AS normalized_date, msg_box AS type, date, _id, read, thread_id, 2 AS msgtype, NULL AS status
FROM pdu
WHERE ((msg_box != 3 AND (m_type = 128 OR m_type = 132 OR m_type = 130)))
GROUP BY thread_id HAVING date = MAX(date)

UNION

SELECT thread_id AS tid, date * 1 AS normalized_date, type, date, _id, read, thread_id, 1 AS msgtype, status
FROM sms
WHERE (sms.type != 3) GROUP BY thread_id HAVING date = MAX(date)
)
GROUP BY tid HAVING normalized_date = MAX(normalized_date)
) threads_tmp
ON threads._id = threads_tmp.thread_id
)
WHERE smsid <> 0 ORDER BY date DESC--;

[解决办法]

SQL code

---如果数据不是实时的话插入临时表#tempSELECT * #temp FROM(  SELECT threads._id, threads_tmp._id AS smsid, threads_tmp.type, threads.read, threads_tmp.msgtype, threads_tmp.status, threads.has_attachment, threads_tmp.msg_read, threads.date, threads.recipient_ids, threads.snippet   FROM threads left JOIN   (SELECT thread_id, type, _id, read AS msg_read, msgtype, status  FROM(SELECT thread_id AS tid, date* 1000 AS normalized_date, msg_box AS type, date, _id, read, thread_id, 2 AS msgtype, NULL AS status  FROM pdu  WHERE ((msg_box != 3 AND (m_type = 128 OR m_type = 132 OR m_type = 130)))   GROUP BY thread_id HAVING date = MAX(date)   UNIONSELECT thread_id AS tid, date * 1 AS normalized_date, type, date, _id, read, thread_id, 1 AS msgtype, status  FROM sms  WHERE (sms.type != 3) GROUP BY thread_id HAVING date = MAX(date))  GROUP BY tid HAVING normalized_date = MAX(normalized_date)) threads_tmp   ON threads._id = threads_tmp.thread_id)   WHERE smsid <> 0 ORDER BY date DESC
[解决办法]
几点意见
1，GROUP BY thread_id HAVING date = MAX(date) 
这样提取的数据有可能会有多条
2，要优化SQL，就不要用GROUP BY
3，连接用UNION 会过滤掉重复的数据，这样效率不是很高
4，建立合适的索引