[SQL ] 有請資料庫高手MYSQL order by 的問題
我有一張表才1900筆資料
inner join 自己 跟別張表
總共join三次 加上兩次sub query
count(a1.aid) as count
如果加上 order by count 排序
速度就會慢整整一半
請問有這種問題該如何解決
才1900筆 就要花時0.16秒左右
目前100個會員就1900筆
30萬個會員 大概就 ....無法想像了
因為用於網站 所以要斤斤計較 拜託了 高手幫幫忙
給大家看一下SQL
SELECT p.ishtar_id, p.name, count( a1.aid ) AS count
FROM (
`answer` AS a1
INNER JOIN `answer` AS a2 ON a1.ishtar_id != a2.ishtar_id
AND a1.qid = a2.qid
AND a1.answer = a2.answer
)
INNER JOIN ishtar_profiles AS p ON p.ishtar_id = a2.ishtar_id
WHERE a1.ishtar_id
IN (
SELECT friend_id AS ishtar_id
FROM friend
WHERE ishtar_id =1
)
AND a2.ishtar_id NOT
IN (
SELECT friend_id AS ishtar_id
FROM friend
WHERE ishtar_id =1
)
GROUP BY a2.ishtar_id
ORDER BY `count` DESC
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 219.84.230.9
※ 編輯: visualPHP 來自: 219.84.230.9 (08/17 18:04)
討論串 (同標題文章)
完整討論串 (本文為第 1 之 4 篇)：