Geek asks and answers

MySQL query optimization: how to optimize vote calculations?

I hope you are fine.

I need help with this database:

enter image description here

This is the database where voices are stored. Users select the audio tracks that they like and they vote for them. They can vote up or down. As easy as pie. But, when it comes to calculating statistics, it gets hairy.

Meta h1>

This is a table with key values ​​that stores the most frequently used statistics (just sorting caching):

mysql> SELECT * FROM Meta;
+-------------+-------+
| Key         | Value |
+-------------+-------+
| TRACK_COUNT | 2620  |
| VOTE_COUNT  | 3821  |
| USER_COUNT  | 371   |
+-------------+-------+

Vote

In the table for voting, the voting itself is conducted. The only interesting field here is Type, the value of which means:

  • 0 - ,
  • 1 - ( )
  • 2 - . , Imported Vote, , , , .

. , , (LikesRP), (DislikesRP), /.

:

  • 5 7
  • 5 7
  • 5 7 , (Vote.Type = 1)
  • 100

100 , :

SELECT
    T.Hash,
    T.Title,
    T.Artist,
    COALESCE(X.VotesTotal, 0) + T.LikesAdjust as VotesAdjusted
FROM (
    SELECT
        V.TrackHash,
        SUM(V.Vote) AS VotesTotal
    FROM
        Vote V
    WHERE
        V.CreatedAt > NOW() - INTERVAL 1 MONTH AND V.Vote = 'up'
    GROUP BY
        V.TrackHash
    ORDER BY
        VotesTotal DESC
) X
RIGHT JOIN Track T
    ON T.Hash = X.TrackHash
ORDER BY
    VotesAdjusted DESC
LIMIT 0, 100;

, ( ). , 5 . № 3:

SELECT
    T.Hash,
    T.Title,
    T.Artist,
    COALESCE(X.VotesTotal, 1) as VotesTotal
FROM (
    SELECT
        V.TrackHash,
        SUM(V.Vote) AS VotesTotal
    FROM
        Vote V
    WHERE
        V.Type = '1' AND
        V.CreatedAt > NOW() - INTERVAL 1 WEEK AND
        V.Vote = 'up'
    GROUP BY
        V.TrackHash
    ORDER BY
        VotesTotal DESC
) X
RIGHT JOIN Track T
    ON T.Hash = X.TrackHash
ORDER BY
    VotesTotal DESC
LIMIT 0, 5;

, 2 , 4 . 200 . , , , . , .

:

  • ? , , ?
  • ?
  • - ?

, , . , , . , SQL ( ).

-, Meta . , . ? , ?

.

, . :

mysql> SHOW INDEXES IN Vote;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Vote  |          0 | UNIQUE_UserId_TrackHash |            1 | UserId      | A         |         890 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          0 | UNIQUE_UserId_TrackHash |            2 | TrackHash   | A         |        4450 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | INDEX_TrackHash         |            1 | TrackHash   | A         |        4450 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | INDEX_CreatedAt         |            1 | CreatedAt   | A         |        1483 |     NULL | NULL   |      | BTREE      |         |
| Vote  |          1 | UserId                  |            1 | UserId      | A         |        1483 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> SHOW INDEXES IN Track;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Track |          0 | PRIMARY        |            1 | Hash        | A         |        2678 |     NULL | NULL   |      | BTREE      |         |
| Track |          1 | INDEX_Likes    |            1 | Likes       | A         |          66 |     NULL | NULL   |      | BTREE      |         |
| Track |          1 | INDEX_Dislikes |            1 | Dislikes    | A         |          27 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
+5
1

, , . , :

? , ?

. OLTP.

?

( ORDER BY ). Vote, :

SELECT  V.TrackHash, SUM(V.Vote) AS VotesTotal
FROM    Vote V
WHERE   V.CreatedAt > NOW() - INTERVAL 1 MONTH AND V.Vote = 'up'
GROUP BY V.TrackHash

2 , TrackHash CreatedAt, Vote Type ( 3 , ). 200 . - , .

- , ?

, , . 3 .

1. ( )

, , . , , , , . , (- ), . .

2. OLAP

, :

CREATE TABLE VoteArchive
(       TrackHash           CHAR(40) NOT NULL,
        CreatedDate         DATE NOT NULL,
        AppMadeUpVotes      INT NOT NULL,
        AppMadeDownVotes    INT NOT NULL,
        ImportedUpVotes     INT NOT NULL,
        ImportedDownVotes   INT NOT NULL,
        MergedUpVotes       INT NOT NULL,
        MergedDownVotes     INT NOT NULL,
    PRIMARY KEY (CreatedDate, TrackHash)
);

,

INSERT VoteArchive
SELECT  TrackHash,
        DATE(CreatedAt),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 0 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 0 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 1 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 1 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Up' AND Type = 2 THEN 1 END),
        COUNT(CASE WHEN Vote = 'Down' AND Type = 2 THEN 1 END)
FROM    Votes
WHERE   CreatedAt > DATE(CURRENT_TIMESTAMP)
GROUP BY TrackHash, DATE(CreatedAt);

. , , , , . , , , . . , .

3.

, . , , "" , , , . /, , , , ( ).

4.

2 , , , 2, , . , , . , , - .

+3

More articles:


All Articles