game_scoreテーブルのサンプル

sample.pl

#!/usr/bin/perl

########################################################################
# Copyright (C) 2016  yoku0825
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
########################################################################

use strict;
use warnings;
use utf8;
use DBI;
use Time::Piece;

my @game_id= (1..10);
my @user_id= (1..10000);

### Configure me.
my $host     = "localhost"; ### use "127.0.0.1" if you want to use TCP connection
my $port     = 3306;
my $socket   = "/var/lib/mysql/mysql.sock";
my $schema   = "d1";
my $user     = "root";
my $password = "";
my $row_count= 300000;

my $dsn= sprintf("dbi:mysql:%s", $schema);
$dsn  .= sprintf(";host=%s", $host) if $host;
$dsn  .= sprintf(";port=%d", $port) if $port;
$dsn  .= sprintf(";mysql_socket=%s", $socket) if $socket;

my $now = Time::Piece::localtime;
my $conn= DBI->connect($dsn, $user, $password) or die;
my $sql = "INSERT INTO game_score (seq, game_id, user_id, play_end_time, score) VALUES (?, ?, ?, ?, ?)";

for (my $n= 1; $n <= $row_count; $n++)
{
  my $real_game_id      = int(rand($#game_id + 1));
  my $real_user_id      = int(rand($#user_id + 1));
  my $real_play_end_time= $now + $n;
  my $real_score        = int(rand(1000000));
  $conn->do($sql, undef, $n, $real_game_id, $real_user_id, $real_play_end_time->strftime("%Y/%m/%d %H:%M:%S"), $real_score);

  print $n, "\n" unless $n % 5000;
}

exit 0;


=pod
mysql57> SHOW CREATE TABLE d1.game_score\G
*************************** 1. row ***************************
       Table: d1.game_score
Create Table: CREATE TABLE `d1`.`game_score` (
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `game_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `play_end_time` datetime NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `game_id` (`game_id`),
  KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=600001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
=cut

重そうなやつ

SELECT game_score.game_id, ANY_VALUE(game_score.user_id) AS user_id, ANY_VALUE(game_score.play_end_time) AS play_end_time, ANY_VALUE(score) AS score FROM game_score JOIN game_score AS dummy USING(score) GROUP BY game_score.game_id ORDER BY user_id ASC, play_end_time DESC, score ASC, user_id DESC, play_end_time ASC, score DESC, user_id ASC, play_end_time DESC LIMIT 3;

重そうなやつ

SELECT game_score.game_id, ANY_VALUE(game_score.user_id) AS user_id, ANY_VALUE(game_score.play_end_time) AS play_end_time, ANY_VALUE(score) AS score FROM game_score JOIN game_score AS dummy USING(score) GROUP BY game_score.game_id ORDER BY user_id ASC, play_end_time DESC, score ASC, user_id DESC, play_end_time ASC, score DESC, user_id ASC, play_end_time DESC LIMIT 3;