From b9b4dfb68d50a0416001665d72b14c26bd8b3235 Mon Sep 17 00:00:00 2001 From: raylu Date: Thu, 24 Oct 2013 23:31:19 -0700 Subject: speed up homepage and kill_list get rid of filesorts --- db/queries.py | 16 +++++++++++----- db/schema.sql | 7 ++++--- 2 files changed, 15 insertions(+), 8 deletions(-) diff --git a/db/queries.py b/db/queries.py index a77fe8f..71f150d 100644 --- a/db/queries.py +++ b/db/queries.py @@ -21,17 +21,22 @@ def search(q): def kill_list(entity_type, entity_id): with db.cursor() as c: + # the combination of DISTINCT and and ORDER BY means we can't join kills or we get filesort + # get kill_ids from chars, then get kill data on those ids kills = db.query(c, ''' - SELECT DISTINCT(kills.kill_id), kill_time, cost, + SELECT DISTINCT kill_id FROM characters + WHERE {}_id = ? ORDER BY kill_id DESC LIMIT 50 + '''.format(entity_type), entity_id) + kill_ids = list(map(operator.itemgetter('kill_id'), kills)) + kills = db.query(c, ''' + SELECT kills.kill_id, kill_time, cost, solarSystemName AS system_name, security, regionName AS region FROM kills - JOIN characters ON characters.kill_id = kills.kill_id JOIN kill_costs ON kill_costs.kill_id = kills.kill_id JOIN eve.mapSolarSystems ON solar_system_id = solarSystemID JOIN eve.mapRegions ON mapSolarSystems.regionID = mapRegions.regionID - WHERE {}_id = ? LIMIT 100 - '''.format(entity_type), entity_id) - kill_ids = list(map(operator.itemgetter('kill_id'), kills)) + WHERE kills.kill_id IN ({}) + '''.format(','.join(map(str, kill_ids)))) char_rows = db.query(c, ''' SELECT kill_id, victim, final_blow, @@ -55,6 +60,7 @@ def kill_list(entity_type, entity_id): characters[kill_id]['attackers'] += 1 if entity_name is None and char[entity_type + '_id'] == entity_id: entity_name = char[entity_type + '_name'] + kills.sort(key=operator.itemgetter('kill_id'), reverse=True) for kill in kills: kill['kill_time'] = _format_kill_time(kill['kill_time']) kill['security_status'] = _security_status(kill['system_name'], kill['security']) diff --git a/db/schema.sql b/db/schema.sql index af21bf5..47102ca 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -31,11 +31,11 @@ CREATE TABLE `characters` ( `weapon_type_id` int DEFAULT NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_char_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`), - INDEX `character_id` (`character_id`), + INDEX `character_id_kill_id` (`character_id`, `kill_id`), + INDEX `alliance_id_kill_id` (`alliance_id`, `kill_id`), + INDEX `corporation_id_kill_id` (`corporation_id`, `kill_id`), INDEX `character_name` (`character_name`), - INDEX `alliance_id` (`alliance_id`), INDEX `alliance_name` (`alliance_name`), - INDEX `corporation_id` (`corporation_id`), INDEX `corporation_name` (`corporation_name`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; @@ -61,5 +61,6 @@ CREATE TABLE `kill_costs` ( `kill_id` int unsigned NOT NULL UNIQUE, `cost` bigint unsigned NOT NULL, PRIMARY KEY (`kill_id`), + INDEX `cost_kill_id` (`cost`, `kill_id`), CONSTRAINT `fk_kill_cost_km` FOREIGN KEY (`kill_id`) REFERENCES `kills` (`kill_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; -- cgit v1.2.3