作者visualPHP (brain)
看板Database
标题Re: [SQL ] 有请资料库高手MYSQL order by 的问题
时间Sun Aug 17 19:36:08 2008
我刚刚从6秒多 降到 0.07秒
原来设定索引不要个别设定 设定成 多栏位的那种索引会比较快
快了大概一半
然後再多设定一点条件 降低她整张表的搜寻
效果一样 从 6秒降到0.07
SQL
SELECT p.ishtar_id, p.name, count( a2.aid ) AS count
FROM ishtar_profiles AS p
INNER JOIN (
`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
) ON p.ishtar_id = a2.ishtar_id
WHERE p.status = '开放中'
AND p.pic = 'Y'
AND p.sex =1
AND (
(
p.age >= (
SELECT avg( p2.age ) -5 AS age
FROM friend AS f2
INNER JOIN ishtar_profiles AS p2 ON f2.friend_id = p2.ishtar_id
AND p2.age !=0
WHERE f2.ishtar_id =00000001 )
AND p.age <= (
SELECT avg( p2.age ) +5 AS age
FROM friend AS f2
INNER JOIN ishtar_profiles AS p2 ON f2.friend_id = p2.ishtar_id
AND p2.age !=0
WHERE f2.ishtar_id =00000001 )
)
OR p.age =0
)
AND a1.ishtar_id
IN (
SELECT friend_id AS ishtar_id
FROM friend
WHERE ishtar_id =00000001
)
AND a2.ishtar_id NOT
IN (
SELECT friend_id AS ishtar_id
FROM friend
WHERE ishtar_id =00000001
)
GROUP BY p.ishtar_id
ORDER BY `count` DESC , p.update DESC
LIMIT 10
※ 引述《starjou (周星星)》之铭言：
: 没有办法 index 的资料要当 order by key，效能大受影响是可想而知的了。
: 其实我想推文的，可是还要等很多很多秒。
: 现在想到的方法是把 group 出来的资料 insert 到某个设好 index 的
: temporary table，然後再排序，不过这样省了 order by 非索引栏位的时间，
: 又多了 insert 的 I/O 时间。
: (或是能用暂存在记忆体的 temporary table，如果 ram 够的话?)
: 如果你有去试的话跟我说一下结果怎麽样？(看能不能真的试个几十万笔的资料)
: 看有没有高手有高招了.....
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 219.84.230.9
1F：推 starjou:眼花了 XD 08/17 19:47
2F：推 slalala:建议不要用NOT IN去做比对 资料量越多效率越囧 08/17 21:40
3F：推 mosquito520:你要不要直接贴个范例资料出来看 跟你要捞什麽资料 08/19 21:19
4F：推 zusocfc:把NOT的东西全部去掉，改个方式去写，不然会占用一堆时间 08/20 16:57