Very slow Left Join on small/medium tables

Question

I have an issue with a particular left join slowing down an important query drastically. Using PHPMyAdmin to test the query, it states that the query took 3.9 seconds to return 546 rows, though I had to wait 67 seconds to see the results which struck me as odd.

There are two tables involved: nsi (1,553 rows) and files (233,561 rows). All columns mentioned in this query are indexed individually as well as there being a compound index on filejobid, category and isactive in the files table. Everything being compared is an integer as well.

The goal of this watered down version of the query is to display the row from the nsi table once and be able to determine if someone has uploaded a file in category 20 or not. There can be multiple files but should only be one row, hence the grouping.

The query:

SELECT 
    nsi.id AS id,
    f.id AS filein
FROM nsi nsi
LEFT JOIN files f
    ON f.filejobid=nsi.leadid AND f.category=20 AND f.isactive=1
WHERE nsi.isactive=1
GROUP BY nsi.id

The 67 second load time for this data is simply unacceptable for my application and I'm at a loss as to how to optimize it any further. I've indexed and compound indexed. Should I just be looking into a new more roundabout solution instead?

Thank you for any help!

Answer 1

This is your query, which I find a bit suspicious, because you have an aggregation but not aggregation function on f.id. It will return an arbitrary matching id:

SELECT nsi.id AS id, f.id AS filein
FROM nsi LEFT JOIN
     files f
     ON f.filejobid = nsi.leadid AND f.category = 20 AND f.isactive = 1
WHERE nsi.isactive = 1
GROUP BY nsi.id;

For this query, I think the best indexes are files(filejobid), category, isactive) and nsi(isactive, filejobid, id).

However you can easily rewrite the query to be more efficient, because it doesn't need the group by (assuming nsi.id is unique):

SELECT nsi.id AS id,
       (SELECT f.id
        FROM files f
        WHERE  f.filejobid = nsi.leadid AND f.category = 20 AND f.isactive = 1
        LIMIT 1
       ) AS filein
FROM nsi 
WHERE nsi.isactive = 1;

The same indexes would work for this.

If you want a list of matching files, rather than just one, then use group_concat() in either query.