pid字段是自动增长列我要搜索某一个确切pid数值的行,放在搜索结果的第一行,另外,还有不包括这一行的其他9个随机行的内容就是如1.2.3.4.5.6……10000,实现一次搜索到9000,45,67,393,8394……我现在方式是:SELECT * , 0 AS rnd
FROM `table`
WHERE pid =1345
UNION SELECT * , rand() AS rnd
FROM `table`
WHERE pid !=1345
ORDER BY rnd
LIMIT 9 但是效率不高,现在表内差不多10000行数据。想过第二种,先搜索table表内行数,然后程序产生随机数,再搜索 where pid in……,但这样的话,如果某些行删除就不行了1、是不是随机降低了效率?请问怎么样效率才能比较高?
FROM `table`
WHERE pid =1345
UNION SELECT * , rand() AS rnd
FROM `table`
WHERE pid !=1345
ORDER BY rnd
LIMIT 9 但是效率不高,现在表内差不多10000行数据。想过第二种,先搜索table表内行数,然后程序产生随机数,再搜索 where pid in……,但这样的话,如果某些行删除就不行了1、是不是随机降低了效率?请问怎么样效率才能比较高?
2、SELECT * , 0 AS rnd FROM `table` WHERE pid =1345
UNION
select a.* from `table` a left join
(SELECT * , 0 AS rnd FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY rnd LIMIT 9
UNION
select a.*,b.rnd from `table` a left join
(SELECT * , rand() AS rnd
FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY b.rnd LIMIT 9
UNION
select * from (
select a.*,b.rnd from `table` a left join
(SELECT * , rand() AS rnd
FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY b.rnd LIMIT 9) a1
UNION
select * from (
select a.*,b.rnd from `table` a left join
(SELECT * , rand() AS rnd
FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY b.rnd LIMIT 8) a1
order by rnd
UNION
select * from (
select a.*,b.rnd from `table` a left join
(SELECT * , rand() AS rnd
FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY b.rnd LIMIT 9) a1
order by rnd
SELECT * , 0 AS rnd FROM `table` WHERE pid =1345) a2
UNION
select * from (
select a.*,b.rnd from `table` a left join
(SELECT * , rand() AS rnd
FROM `table` WHERE pid =1345) b
on a.pid=b.pid where b.pid is null ORDER BY b.rnd LIMIT 9) a1
order by rnd
iRnd = rand()*iMaxPid
select * from table where pid<=irnd order by pid desc limit 1;这样用10个查询,速度应该会更快。
select a.* from tt a inner join lsb b on a.pid=b.id
然后再
生成如下SQL语句也可,这样会形成对primay key 的利用。
(SELECT * FROM `table` WHERE pid =1345)
UNION ALL
(SELECT * FROM `table` WHERE pid <=8888 order by pid desc limit 1)
UNION ALL
(SELECT * FROM `table` WHERE pid <=7777 order by pid desc limit 1)当然这种语法不确保没有重复。如需减少重复的概率则可取20个然后再得用rand()(SELECT * FROM `table` WHERE pid =1345)
UNION ALL
select *,rand() as rnd
from (
(SELECT * FROM `table` WHERE pid <=8888 order by pid desc limit 1)
UNION ALL
(SELECT * FROM `table` WHERE pid <=7777 order by pid desc limit 1)
UNION ALL
(SELECT * FROM `table` WHERE pid <=6666 order by pid desc limit 1)
UNION ALL
(SELECT * FROM `table` WHERE pid <=9999 order by pid desc limit 1)
UNION ALL
(SELECT * FROM `table` WHERE pid <=4444 order by pid desc limit 1)
) x
order by rnd
limit 2