RE: RE: HiveSQL: Author Rewards & Curation Rewards
You are viewing a single comment's thread from:

RE: HiveSQL: Author Rewards & Curation Rewards

Words
105
Reading
1 min
Listen
Play
4y

You should not use parent_author='' to differentiate posts from comments but depth=0!
The depth column has its own index and numeric value comparisons are way faster than string comparisons, especially on a big number of rows.

It's also good practice to explicitly specify which JOIN type you use and to have all join conditions in the JOIN clause rather than splitting them into the WHERE clause.

Finally, you are making your JOIN in the wrong order. The main table should be TxVotes (table in which you are looking for info) and Comments should come next as it's a table you use to filter and get alternate data.

SELECT 
    TxVotes.author,
    TxVotes.permlink, 
    Comments.title,
    TxVotes.timestamp
FROM 
    TxVotes
    INNER JOIN Comments ON 
        Comments.Author = TxVotes.Author
        AND Comments.permlink = TxVotes.permlink
WHERE 
    TxVotes.voter = 'geekgirl'
    AND Comments.depth = 0
    AND TxVotes.timestamp BETWEEN '2022-02-01' AND '2022-03-01'
ORDER BY 
    TxVotes.timestamp ASC