I am currently working on a project for my studies where a wordcloud is created for the NYT. Therefore I am scraping all the articles from the NYT from their API, and then store dates of the article, the articles href and all the tokens that were mentioned inside that article with their amount in a mysql database. The model looks like the following:

Date (dateID PK, publish_date Date)
Article (articleID PK, href char(300), dateID FK)
Token (tokenID PK, name char(100), articleID FK)

I have also indexed the publish_date and the tokens name which has already made the query twice as fast ish.

The job of the query is, to return up to 100 tokens with their total amount within all articles that were published within a specifies range of dates. It looks like this:

    dateID as (
        SELECT dateID
        FROM date 
        WHERE publish_date >= '1999-12-31'
          AND publish_date <= '2023-12-31'
    articleIDs as (
        SELECT articleID
        FROM article
        WHERE dateID IN (TABLE dateID)
SELECT t.name as name, sum(t.amount) as amount
FROM token t
WHERE t.articleID IN (TABLE articleIDs)
AND t.name > '@'
GROUP BY t.name
LIMIT 100;

FYI: It's an innoDB hosted by aws with mysql version 8.0.33

I have already limited the timeframe fetched from the NYT and right now there are the years 2012 - 2023 loaded into the database. With the current setup, the query takes around 100 seconds to complete with only 6,458,501 tokens. I have found, that the most inefficient part is the group by so I looked into optimization via index scan, but have found that this would not work for my usecase.

My goal would be something around 5 seconds.

EDIT: Execution plan: Execution plan

  • Could you share the execution plan (EXPLAIN) ? Dec 13, 2023 at 19:55


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.