MySQL right join with tables with 100's of 1000's of rows The following join is taking far too long in MySQL, almost half a day and it’s still not finished:
INSERT INTO `aaaaa` 
SELECT * 
FROM `chosenLang` 
RIGHT JOIN `chosenLOCC_P` 
    ON SUBSTRING_INDEX(SUBSTRING_INDEX
      (SUBSTRING_INDEX(`chosenLang`.`call`,'.',1),' ',1),'*',1) 
       LIKE concat(`chosenLOCC_P`.`code`,`chosenLOCC_P`.`reference`)

chosenLang has  413826 rows, 42 columns (38 of them longtext, 3 of them varchar, 1 of them int(11)). call is varchar.
chosenLOCC_P has 117077 rows, 23 columns (21 of them longtext, 2 of them int(10)).
What can I do to maximise the speed of this query?
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.