John John - 1 month ago 8
SQL Question

Milion Record Sql Order By and count rows

I have a database with millions of records.
The table is structured as:




Table name : record

Filed1: Name (varchar)(Primary key)

Field2: Record(int/bigint)

example:

Name | Record

Darrin | 256

Aaron | 3

Daryl | 12

...




I need to know what position does the user with the name 'namex' in sorted records.

Currently i implement this solution:

...

$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++;
}
}

...


With it and 1 million records in the database, the answer comes in about 4 second.

I tried to put a table index on the field Record but have remained the same performance.

how can I reduce the execution times?

Answer

Since I don't know what DBMS you are using for (in tags you are using both mysql and sql-server...), you can you create a view (for SQL server have to be a indexed view) or for mysql implement/emulate a kind of materialized view (has a better performance). The view is good to get a better perfomance through some DBMS. For MySQL may have no difference.

In the view show up the rank position as the query below (mysql example):

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    @curRank := @curRank + 1 AS rank
FROM 
    record,
    (SELECT @curRank := 0) r
ORDER BY Record DESC;

or SQL server:

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    row_number() over(ORDER BY record)
FROM 
    record;

And just run your query:

SELECT name , rank FROM ranked_record WHERE name LIKE 'some name'

Update:

After John comments, I've realized about the error from views using variables. It isn't possible due the "feature/bug" of/from MySQL

Due this, you can choose about use this as subquery in FROM clause:

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';

OR create a function to count the rank inside the view (like this example):

CREATE FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
  SET @var := IFNULL(@var,0) + 1;
  return @var;

end;

Then, create your view as before, just using the function instead of the variable:

CREATE VIEW ranked_record AS
SELECT 
    record.Name,
    func_inc_var_session() as rank
FROM 
    record
ORDER BY Record DESC;
Comments