作者visualPHP (brain)
看板Database
标题[SQL ] 有请资料库高手MYSQL order by 的问题
时间Sun Aug 17 17:58:01 2008
我有一张表才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)