I'm trying to run a query in MySQL that's timing out after a couple of minutes on a QA system with 8 million+ rows. It runs fine for me locally, but obviously less data.

Here's the query:

SELECT 
    system_name as systemName, 
    systemLabel,  
    feature_vector as featureVector, 
    code as norcaCode,
    count(1) as sum 
    FROM (
        SELECT a.id, 
        a.object_id, 
        a.system_name, 
        d.label as systemLabel, 
        b.norca_type AS norcaType, 
        b.feature_vector, 
        a.seqnb, 
        a.object_index, 
        c.code 
    FROM 
        system_objectdata a  
    JOIN 
        sick_il_dacq.system_barcode_norca  b 
    ON 
        a.id = b.system_objectdata_id  
    AND 
        a.partition_key  = b.partition_key  
    LEFT JOIN 
        system_feature_vector c  
    ON 
        b.feature_vector = c.value 
    JOIN 
        sick_il_services.system_config d  
    ON 
        a.system_name = d.name  

    WHERE LEFT(FROM_UNIXTIME(object_scan_time/1000),10) >= SUBDATE(CURRENT_DATE, 100) 
    AND 
        norca_type = 'BARCODE' 
     AND 
        a.is_duplicate = 0 

    ) detail 
    
    GROUP BY 
        system_name, feature_vector, norcaCode;

Here's the explain plan:

It looks like the link to table d, system_config, is has no possible keys. However, there is an index for name on the table:

Any idea why it's not using the name index? And in general, any ideas on how to improve the query speed?