How to make a table with the result of calculated rankings?

I'd like to make new table with the result of calculated rankings from a table. I work with PHP and MySQL.

I got some codes from googling and it works on the screen.

Select User_Id, Score, @rank := @rank + 1 as Ranking
    from Rankings, (select @rank := 0) XX
    order by Score desc

I'd like to know how to create new table with these data, so that I can see the rankings anytime I want. And I wonder if this code is good for big data (lots of records).

Can anyone help this beginner? Thanks in advance for any help.

Solution

1: CREATE TABLE ... SELECT Syntax: You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement.

CREATE TABLE new_table_name AS
SELECT User_Id, Score, @rank := @rank + 1 AS Ranking
FROM Rankings, (SELECT @rank := 0) XX
ORDER BY Score DESC;

Note: This will auto-detect column datatype and column names based on the select query and sometimes they are not proper.

To create desired column names and their data types, you can mention the same in CREATE TABLE clause.

CREATE TABLE new_table_name (user_id INT NOT NULL, rank INT)
SELECT User_Id, Score, @rank := @rank + 1 AS Ranking
FROM Rankings, (SELECT @rank := 0) XX
ORDER BY Score DESC;

2: INSERT ... SELECT Syntax: This will be used to store data of a select statement in the existing table.

INSERT INTO existing_table_name (user_id, rank)
SELECT User_Id, Score, @rank := @rank + 1 AS Ranking
FROM Rankings, (SELECT @rank := 0) XX
ORDER BY Score DESC;