Optimize query between two MySql tables
Hi Max and welcome to DBA.SE.


  "when reviewing the execution plan since I have many records, it takes
  too long to go through all the records of both tables, so how can I
  optimize it?"


Are you having a real performance issue or are you assuming you are going to have one? If your query needs the data from all rows, there is no magic trick that MySQL can do and not scan each and every one. If this is a real production challenge, we will need to see your execution plans for the real data. If it's not, I wouldn't worry about it too much, but there are several things you can do to help the optimizer and allow more efficient access methods.


Instead of the derived table for the months, I would create a permanent table for months with just 12 rows. probably the month number and its name and index both. This will make the query more readable, and might change the optimizer choice when the set is indexed. you will most likely use it in other places as well. I also like to create full calendar tables in my databases, see for example this script. It's a SQL Server T-SQL script, so you may need to adjust it slightly to work in MySQL.
The optimal index for your query is on (date, amount). The latter key is needed to cover the query so the optimizer can get all it needs directly from the index without performing lookups.
If you can upgrade to MySQL 8, it offers functional indexes where you can create an index on (MONTH(date), amount) directly, which will speed up this query, but may hurt modification performance like any other index.


HTH