MySQL join table with itself with OR condition

Question

I have a table FOLDERS, defining smth like directory structure. It contains the columns: ID, PARENT_ID and LINK_ID.

I cannot change the application which I have a problem now with a slow query.

This application has a logic that PARENT_ID could refer both to ID or LINK_ID.

Also, I cannot change the query, that I found slows down the whole our ETL process:

SELECT  folders.ID AS OrigFolderID, parentfolder.ID, parentfolder.Name 
FROM    folders 
          LEFT JOIN folders AS parentfolder ON folders.ParentID=parentfolder.ID OR folders.ParentID=parentfolder.LinkID
WHERE   folders.ID IN                (112450385,188823933,211307470,211403833,211545367,212449523,212539966)

We can change database however, removing or adding indexes.

Are there any chance to speed up this query?

It seems that the indexes on "parentfolder" are not working (there are indexes on LinkID, on ParentId, and on ID (which is a primary key), and the table is fully scanned. There are 200K rows in it.

Answer 1

I assume ID and LinkID are each indexed.

OR is the killer. You must find a way to change the query; no tuning, etc, can fix the performance problem.

LEFT is suspicious -- Do you want NULLs if there is no parent?

The OR can be turned into UNION DISTINCT of two selects -- one with either side of the OR.