![]() However, MySQL's optimizer has some weak spots, one of which is that SELECT. If we were using Oracle, I wouldn't expect this technique to make a difference (as Oracle's optimizer is pretty smart). Apply to original table the materialized (temp) ranks. Apply a PK index to the temp table for performance.ĪLTER TABLE temp_ranks ADD PRIMARY KEY (id) Cleanup any old temporary table structureĭROP TEMPORARY TABLE IF EXISTS temp_ranks It may be more performant to make a real (materialized) temp table instead of the inline subquery. How can I optimize this query to run faster? The problem is, however, with some hundreds of thousands of entries, this query runs for a couple of days, even though I have created indexes for the WHERE conditions. I am currently achieving this goal with this query: UPDATE archive_league If 2 players share the same score, they should receive the same rank (olympic scoring).Įxample: player x with score 528 receives rank 7 For example the player with the highest score should receive rank = 1, 2nd highest score rank = 2 and so on. My goal is to go through all entries for a given date and assign the field "rank" for results. Rank - the position of the player, descending by score, in the given quarterĭate - the date of the quarter this entry represents Score - the score of the user for the given quarter Rounds - the number of games the user played in the given quarter It comes with the following fields: id - the id of an entry ![]() Each entry represents a user and his score for a given league quarter. Every 3 months, the current season ends and all scorings of the current quarter are archived into a table called archive_league. In our games we have a quartely league system. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |