MySQL right join with tables with 100's of 1000's of rows
Make new columns to handle those clumsy expressions.
Make them reasonably sized VARCHARs, not LONGTEXT.
Write a 1-time script to populate those column(s).
Change code so that future INSERTs (etc) maintain the columns.
Add a suitable index so that the JOIN works efficiently.
Use JOIN unless you really need RIGHT JOIN.  Are you currently hoping to get a lot of NULL columns.