Milion Record Sql Order By and count rows

Question

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?

`SELECT COUNT(Name) as Rank from record WHERE Name <= $name` perhaps... of course you need to parameterise the query to prevent risks of SQL injection — Mark Baker, Oct 28 '16 at 16:02
you really should be using a `where` clause. You're LITERALLY doign the equivalent of driving to a walmart, buying up the ENTIRE inventory of the store, driving it all home, then picking through it and throwing away everything except the chocolate bar you wanted. — Marc B, Oct 28 '16 at 16:16
Thanks , i resolve with : mysqli_query($mysqli,"SELECT COUNT(Name) as rank FROM record Where Record >= '$record'"); — John, Oct 28 '16 at 16:37
That looks pretty good. But what if two users have the same value for "record"? — Kevin_Kinsey, Oct 28 '16 at 18:17

score 1 · Accepted Answer · edited May 23 '17 at 10:27

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;

I get this error : " #1351 - View's SELECT contains a variable or parameter " — John, Oct 28 '16 at 17:31
@John My mistake, I'll update the answer with the correct version. — Gabriel Heming, Oct 28 '16 at 17:44
MySQL subqueries tend to suck (time, I mean...), don't they? — Kevin_Kinsey, Oct 28 '16 at 18:16
@Kevin_Kinsey Since MySQL doesn't have any kind of cache for views (as materialized), there's any no difference between subquery from and a view. But, in this case the view add one or two steps more to the query execute, views is probaly slower — Gabriel Heming, Oct 28 '16 at 18:21

VVN · Answer 2 · 2016-10-28T16:48:11.823

0

We can write this in sql query instead of fetching the records and looping it in PHP.

select row = row_number() over( order by Record ) , * from record where name like 'namex'

edited Oct 28 '16 at 16:48

answered Oct 28 '16 at 16:34

VVN

501
1
9
21

The OP mis-tagged the question, it has nothing to do with ms sql server. Pls remove this answer. – Shadow Oct 28 '16 at 16:42
Does this give the correct row_number? Or does it always give "1"? – Rick James Oct 28 '16 at 21:27

score -1 · Answer 3 · answered Oct 28 '16 at 16:31

-1

I dont really understand why are you looping every record knowing that the column "Name" its your PK (unique values).

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

answered Oct 28 '16 at 16:31

Max Dominguez

174
1
2

This is a poor approach. The where clause is absolutely the right way to go but this is wide open to sql injection. This needs to be a parameterized query. – Sean Lange Oct 28 '16 at 16:36
Thanks for your comment. Can you provide a proof of concept injecting SQL code on this sentence?. I think that the function mysql_real_escape_string is already preventing that kind of attack. – Max Dominguez Oct 28 '16 at 16:47
The real problem is that this doesn't solve the issue, as near as I can tell. – Kevin_Kinsey Oct 28 '16 at 18:16
No. Escaping a string is not going to prevent that. There are far more sinister things that can be passed besides extra single quotes. You can pass in binary and it will be executed. Take a look here. http://stackoverflow.com/questions/22304930/is-mysqli-real-escape-string-safe – Sean Lange Oct 28 '16 at 18:26
Thanks @Sean Lange :), i'm really interested in this kind of concept but in the link that you provide nobody mention anything about binary code injection through mysqli_real_escape_string. I recognize that for a generic function, the use of parametized querys is more efficient so we can validate data by type but in this case we are handling strings. Just asking to learn something new. – Max Dominguez Oct 28 '16 at 20:42
I realize they don't specifically mention binary code. The point is that simply replacing ' with '' is nowhere good enough to prevent sql injection. There are so many ways through a simple replace it isn't even funny. – Sean Lange Oct 28 '16 at 20:45
I get `1` because the `WHERE` happens before the `COUNT`. – Rick James Oct 28 '16 at 21:31

Milion Record Sql Order By and count rows

3 Answers3