我有一个包含数百万条记录的数据库。表的结构如下:
表名:record
Filed1: Name (varchar)(Primary key)
Field2:记录(int/长整型数字)
的例子:
Name | Record
Darrin | 256
Aaron | 3
Daryl | 12
…
我需要知道在排序记录中名称为'namex'的用户的位置。
目前我实现这个解决方案:
...
$name=namex;
$query= mysqli_query($mysqli,"SELECT Name FROM record ORDER BY Record DESC");
$x=0;
$rank=0;
if ($query->num_rows > 0) {
// output data of each row
while($row = $query->fetch_assoc()) {
if($row["Name"]==$name){
$rank=$x+1;
echo "Rank : $rank<br>";
break;
}
$x++;
}
}
...
有了它和数据库中的100万条记录,答案大约在4秒内出现。
我尝试在字段Record上添加一个表索引,但性能保持不变。
如何减少执行时间?
因为我不知道你使用的是什么DBMS(在标签中你使用mysql和SQL -server…),你可以创建一个视图(SQL服务器必须是索引视图)或mysql实现/模拟一种物化视图(具有更好的性能)。该视图有助于通过某些DBMS获得更好的性能。对于MySQL可能没有区别
在视图中显示排名位置,如下所示(mysql示例):
CREATE VIEW ranked_record AS
SELECT
record.Name,
@curRank := @curRank + 1 AS rank
FROM
record,
(SELECT @curRank := 0) r
ORDER BY Record DESC;
或SQL server:
CREATE VIEW ranked_record AS
SELECT
record.Name,
row_number() over(ORDER BY record)
FROM
record;
然后运行你的查询:
SELECT name , rank FROM ranked_record WHERE name LIKE 'some name'
更新:
在John注释之后,我意识到视图使用变量的错误。由于/from MySQL
的"feature/bug",这是不可能的。因此,您可以选择是否将此用作FROM子句中的子查询:
SELECT
name,
rank
FROM (
SELECT
record.Name,
@curRank := @curRank + 1 AS rank
FROM
record,
(SELECT @curRank := 0) r
) AS ranked_record
WHERE
name LIKE 'some name';
或者创建一个函数来计算视图内的排名(如本例):
CREATE FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
SET @var := IFNULL(@var,0) + 1;
return @var;
结束;然后,像前面一样创建视图,只是使用函数而不是变量:
CREATE VIEW ranked_record AS
SELECT
record.Name,
func_inc_var_session() as rank
FROM
record
ORDER BY Record DESC;
我们可以在sql查询中编写,而不是在PHP中获取记录并循环它。
select row = row_number() over( order by Record ) , * from record where name like 'namex'
我真的不明白为什么你要循环每条记录,知道列"Name"是你的PK(唯一值)。
$name="Darrin";
$query= mysqli_query($db_connection, "SELECT COUNT(1) as rank FROM record WHERE Name = '".mysqli_real_escape_string($name)."' ORDER BY Record DESC");
$row = mysqli_fetch_row($query);
if ($query->num_rows > 0) {
echo "Rank : $row["rank"]<br>";
}