mysql自动增长列，搜索问题-(初级)

[amani11]

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、是不是随机降低了效率？请问怎么样效率才能比较高？

[WWWWA]

SELECT * , 0 AS rnd FROM `table` WHERE pid =1345 UNION SELECT * , rand() AS rnd FROM `table` WHERE pid !=1345 ORDER BY rnd LIMIT 9

1、在PID上建立索引；
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

[WWWWA]

SELECT * , 0 AS rnd FROM `table` WHERE pid =1345
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

[amani11]

你好，这个，我把union两边分开执行，效率是高了很多，【在phpmyadmin里试的】

但是合在一起，有错误，提示“Unknown table 'b' in order clause”

[WWWWA]

SELECT * , 0 AS rnd FROM `table` WHERE pid =1345
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

[WWWWA]

SELECT * , 0 AS rnd FROM `table` WHERE pid =1345
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

[WWWWA]

SELECT * , 0 AS rnd FROM `table` WHERE pid =1345
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

[WWWWA]

select * from (
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

[ACMAIN_CHM]

由于你使用了rand()做为条件这样，MySQL就无法再根据任何索引来优化查询了。所有肯定会花这么多时间。 不如你的第二个方案，在程序中生成随机数，来实现 相同的10条随机记录获取。

iMaxPid = select max(pid) from table

for (i=0;i<10;i++)
iRnd = rand()*iMaxPid
select * from table where pid<=irnd order by pid desc limit 1;

这样用10个查询，速度应该会更快。

[WWWWA]

然后程序产生随机数，再搜索 where pid in……，但这样的话，如果某些行删除就不行了

你将产生的数存入临时表中(lsb )，再与工作表连接
select a.* from tt a inner join lsb b on a.pid=b.id

[ACMAIN_CHM]

按照8楼，生成10个rand()
然后再
生成如下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