--
-- Table structure for table `player_kit`
--

CREATE TABLE `player_kit` (
  `id` TINYINT UNSIGNED NOT NULL,
  `pid` INT UNSIGNED NOT NULL,
  `time` INT UNSIGNED NOT NULL DEFAULT 0,
  `kills` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  `deaths` MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY(`pid`,`id`),
  FOREIGN KEY(`pid`) REFERENCES player(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(`id`) REFERENCES kit(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `bf2stats`.`player_kit` ADD INDEX `reverse_ids` (`id`, `pid`);

--
-- My Full Scanning Query
--
SELECT p.name, p.rank, p.country, k.pid, k.kills, k.deaths, k.time
FROM player_kit AS k
  INNER JOIN player AS p ON k.pid = p.id
WHERE k.id = 0 AND k.kills > 0
ORDER BY kills DESC, time DESC
LIMIT 0, 40

--
-- EXPLAIN results by MySQL
--
+----+-------------+-------+------+---------------+-------------------------------------+-------------+-----+----------------+-------+
| id | select_type | table | type | possible_keys | key                                 | key_len     | ref | rows           | extra |
+----+-------------+-------+------+---------------+-------------------------------------+-------------+-----+----------------+-------+
|  1 | SIMPLE      | k     | NULL | ref           | PRIMARY,`kit_kill_time`,reverse_ids | reverse_ids | 1   | const          | 75    |
|  1 | SIMPLE      | p     | NULL | eq_ref        | PRIMARY                             | PRIMARY     | 4   | bf2stats.k.pid | 1     |
+----+-------------+-------+------+---------------+-------------------------------------+-------------+-----+----------------+-------+
--
-- Additional Tables just in case, for reference
--
--
-- Table structure for table `kit`
--

CREATE TABLE `kit` (
  `id` TINYINT UNSIGNED,
  `name` VARCHAR(32) NOT NULL,
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `player`
--

CREATE TABLE `player` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) UNIQUE NOT NULL,
  `rank` TINYINT NOT NULL DEFAULT 0,
  `country` CHAR(2) NOT NULL DEFAULT 'xx',
  PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;