[help] need to increase efficiency and obtain the maximum value from multiple selects
(1) By Adam (astranberg) on 2021-11-08 23:32:30 [link] [source]
Hey all, First of all, I really appreciate everybody's help. I'm new to SQLite and I'm sure there's a more efficient way to do what I'm wanting. I tried executing the first example below, but after four hours it still hadn't finished. And it doesn't even do half of what I want! UPDATE players SET max_trophies = (SELECT winner_trophies FROM battles WHERE winner_tag == players.player_tag ORDER BY winner_trophies LIMIT 1) UPDATE players SET max_trophies = (SELECT loser_trophies FROM battles WHERE loser_tag == players.player_tag ORDER BY loser_trophies LIMIT 1) Here are the tables for reference: CREATE TABLE players( player_tag TEXT, update_date TEXT, max_trophies TEXT, UNIQUE(player_tag)) CREATE TABLE battles( battle_time TEXT, battle_type TEXT, winner_tag TEXT, winner_trophies INTEGER, winner_decktype TEXT, winner_cards TEXT, loser_tag TEXT, loser_trophies INTEGER, loser_decktype TEXT, loser_cards TEXT, UNIQUE(battle_time,winner_tag,loser_tag)) My goal is to update the max_trophies in the player table to the highest trophies they have been attributed in a list of numerous battles. This means to select winner_trophies where winner_tag is the player_tag AND to select loser_trophies where loser_tag is the player_tag, then set the winner_trophies to the maximum value. One step further would be to only do this if the maximum value is greater than the current max_trophies. I'm reading into the MAX and HAVING clauses and will update if I have other ideas. Thanks!
(2) By Adam (astranberg) on 2021-11-08 23:50:44 in reply to 1 [link] [source]
I think the use of max and group by will help here: UPDATE players SET max_trophies = (SELECT max(winner_trophies) FROM battles WHERE winner_tag == players.player_tag AND winner_trophies > max_trophies GROUP BY winner_tag) UPDATE players SET max_trophies = (SELECT max(loser_trophies) FROM battles WHERE loser_tag == players.player_tag AND loser_trophies> max_trophies GROUP BY loser_tag) Is there a more efficient way?
(3) By ddevienne on 2021-11-09 07:04:56 in reply to 1 [source]
It's not your queries the main problem, it's your indexes.
You are joining on winners and losers, which are not indexed in a usable way.
Just switching around the battles'
UNIQUE index to put
will speed up one of your two queries. The other should be indexed separately (non-unique).
Or keep your
UNIQUE index as-is, and index both
battles' winners and losers non-unique separately (i.e. 2 indexes).
From the queries you are showing, seems like these two indexes would help most:
CREATE INDEX battles_winners on battles (winner_tag, winner_trophies); CREATE INDEX battles_losers on battles (loser_tag, loser_trophies);
That way, joining will be fast (tags are first), and the index contains
the desired trophies in order, so the
ORDERY BY LIMIT 1 (or just
should be fast, and no need to access the
battles table at all.