Hallo Thomas,
ich habe die Infos mal zusammengestellt:
SHOW CREATE TABLE highscores;
+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| highscores | CREATE TABLE `highscores` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`spielerId` int(11) NOT NULL,
`levelId` int(11) NOT NULL,
`spielmodus` int(11) DEFAULT NULL,
`xp` int(11) NOT NULL,
`spiele` int(11) NOT NULL,
`niederlagen` int(11) NOT NULL,
`siege` int(11) NOT NULL,
`unentschieden` int(11) NOT NULL,
`position` int(11) DEFAULT NULL,
`positionAlt` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `levelId` (`spielerId`,`levelId`,`spielmodus`),
KEY `idx_spielmodus` (`spielmodus`)
) ENGINE=InnoDB AUTO_INCREMENT=15761848 DEFAULT CHARSET=utf8 |
+----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW TABLE STATUS LIKE 'highscores';
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| highscores | InnoDB | 10 | Compact | 2284107 | 163 | 374325248 | 0 | 141574144 | 1236271104 | 15761848 | 2012-08-08 03:06:58 | NULL | NULL | utf8_general_ci | NULL | | |
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
EXPLAIN SELECT positionAlt, position FROM highscores WHERE spielmodus IS NULL ORDER BY siege DESC, niederlagen ASC, unentschieden DESC;
+----+-------------+----------------------------+------+-----------------+-----------------+---------+-------+---------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+------+-----------------+-----------------+---------+-------+---------+-----------------------------+
| 1 | SIMPLE | highscores | ref | idx_spielmodus | idx_spielmodus | 5 | const | 1142053 | Using where; Using filesort |
+----+-------------+----------------------------+------+-----------------+-----------------+---------+-------+---------+-----------------------------+
SHOW VARIABLES LIKE '%buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+---------+
Noch eine weitere Info - am längsten dauert das eingangs erwähnte Update bei der Spielmodus-übergreifenden Positionsberechnung ("WHERE spielmodus IS NULL"). Damit werden die Resultate aus allen Spielmodi zusammengefasst, was natürlich die meisten Datensätze dann sind.
Ich bin für alle Tipps dankbar!