Re: Optimisation problem with left join

[Rick James]

SELECT  t.teamid AS useid, IF(pt.prevname IS NULL,
               t.teamname,pt.prevname) AS usename
    FROM  teams t
    LEFT JOIN  prevteams pt ON t.teamno = pt.teamno
      AND  pt.fromssn <= '142'
      AND  pt.tossn >= '142'
    WHERE  pt.url_prevname = 'arsenal'
      OR  t.url_teamname = 'arsenal';

Problems:
* LEFT JOIN prevents starting with pt, so the query optimizer is stuck starting with t.
* There is nothing that can be done with t because of the OR.

Perhaps this will work, and be faster:

SELECT  t.teamid AS useid, pt.prevname AS usename
    FROM  teams t
    JOIN  prevteams pt ON t.teamno = pt.teamno
      AND  pt.fromssn <= '142'
      AND  pt.tossn >= '142'
    WHERE  pt.url_prevname = 'arsenal'
    UNION  DISTINCT 
SELECT  t.teamid AS useid, IF(pt.prevname IS NULL,
                t.teamname,pt.prevname) AS usename
    FROM  teams t
    LEFT JOIN  prevteams pt ON t.teamno = pt.teamno
      AND  pt.fromssn <= '142'
      AND  pt.tossn >= '142'
    WHERE  t.url_teamname = 'arsenal';

It will need some indexes:
pt: INDEX(url_prevname, fromssn), INDEX(url_prevname, tossn)
t: INDEX(url_teamname)

If you don't understand the importance of "compound" indexes, study
http://mysql.rjweb.org/doc.php/index1

I don't know whether UNION ALL (slower) or UNION DISTINCT (faster) is appropriate for your situation.