SELECT member_id FROM mm_view_scene WHERE scene_id = 30196
这个子查询大概会返回1000条数据,用IN判断可能效率比较低。不知道postgresql中有没有二分查找的函数?如果有的话我考虑把子查询排序,然后用二分来查找
这个子查询大概会返回1000条数据,用IN判断可能效率比较低。不知道postgresql中有没有二分查找的函数?如果有的话我考虑把子查询排序,然后用二分来查找
id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
PRIMARY KEY (id1, id2)
);-- 插入测试数据
CREATE OR REPLACE FUNCTION
insert_data ()
RETURNS INTEGER AS
$$
DECLARE
v_temp1 INTEGER;
v_temp2 INTEGER;
v_temp3 INTEGER;
BEGIN
FOR v_temp1 IN 0 .. 999 LOOP
FOR v_temp2 IN 0 .. 999 LOOP
v_temp3 := (random() * 1000)::INTEGER % 1000;
IF v_temp3 < 200 THEN
INSERT INTO test (id1, id2) VALUES (v_temp1, v_temp2);
END IF;
END LOOP;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
SELECT insert_data();-- 查询1
SELECT t.id1, COUNT(*) FROM test t, (SELECT id2 FROM test WHERE id1 = 500) AS r
WHERE t.id2 = r.id2
AND t.id1 <> 500
GROUP BY t.id1
ORDER BY COUNT(t.id1) DESC
LIMIT 10;
-- 查询2
SELECT id1, COUNT(*) FROM test WHERE id2 IN (
SELECT id2 FROM test WHERE id1 = 500
)
AND id1 <> 500
GROUP BY id1
ORDER BY COUNT(id1) DESC
LIMIT 10;
CREATE TABLE test1
(
id2 int4 NOT NULL,
id1 int4 NOT NULL,
PRIMARY KEY (id2, id1)
);
数据和test一模一样。
buy_count信息则放到相关产品的表格中,每次有购买时就更新,这样可以省去统计的开销。
CREATE TABLE book
(
id int4 NOT NULL,
name varchar(10) NOT NULL,
buy_count INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (id1)
);SELECT b.id, b.buy_count FROM book b, (
SELECT id1 FROM test1 WHERE id2 IN
( SELECT id2 FROM test WHERE id1 = 5000 ) GROUP BY id1) r
WHERE b.id = r.id1 AND b.id <> 5000
ORDERY BY buy_count DESC
LIMIT 10;现在1000万条数据查询不足一秒。主要是因为id2排在test1的主键的前面,在作IN查询时速度极快,由于这个查询的结果记录已经不多,剩下的查询并ORDER都是很快的。