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!

You only need one index and if its bad the query optimizer will pick a better one. You should remove individual indexes on each column that's unnecessary overhead. Plus remove that compound index. using isactive when I'm sure it's a 1 or 0 so two possible cases grabs your entire data set instead of partitions. Based on this watered down query I would put your index on nsi.id ONLY. — FirebladeDan, Jul 31 '15 at 15:19
I have no idea what you think you're trying to do. If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jul 31 '15 at 15:26
FirebladeDan actually solved it exactly to my needs. Removing the compound and isactive indexes sped it up drastically. I had not considered that more indexes would be used in that manner to create more work for SQL. Interesting stuff. Thank you, FirebladeDan! — BZohar, Jul 31 '15 at 15:28

score 2 · Accepted Answer · answered Jul 31 '15 at 15:31

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.

Very slow Left Join on small/medium tables

1 Answers1