Interpreting mysql EXPLAIN ANALYZE

MySQL & MariaDB Other MySQL® Questions
1

Would like to have a complete explanation of the below output from EXPLAIN ANALYZE:

 -> Sort row IDs: users.userid  (actual time=32773.650..32801.255 rows=7747 loops=1)
    -> Table scan on <temporary>  (cost=0.00..0.50 rows=6083517) (actual time=0.017..7.595 rows=7747 loops=1)
        -> Temporary table with deduplication  (cost=1903155.27..1903155.77 rows=6083517) (actual time=32764.224..32772.367 rows=7747 loops=1)
            -> Nested loop left join  (cost=1294803.53 rows=6083517) (actual time=0.139..5100.313 rows=2547647 loops=1)
                -> Nested loop left join  (cost=682971.50 rows=6083517) (actual time=0.130..1084.932 rows=2547647 loops=1)
                    -> Nested loop left join  (cost=64598.37 rows=23030) (actual time=0.109..158.321 rows=7747 loops=1)
                        -> Nested loop left join  (cost=41321.81 rows=5475) (actual time=0.095..117.349 rows=7747 loops=1)
                            -> Nested loop inner join  (cost=38336.42 rows=5457) (actual time=0.069..48.246 rows=7747 loops=1)
                                -> Filter: ((CV.configuration_value = 1) and (CV.tenant_id is not null))  (cost=137.02 rows=53) (actual time=0.041..2.120 rows=3 loops=1)
                                    -> Index lookup on CV using X_tcv_tenant_configuration_id (tenant_configuration_id='236')  (cost=137.02 rows=527) (actual time=0.035..2.061 rows=527 loops=1)
                                -> Filter: ((users.grp = 3) and (users.`status` <> 8))  (cost=517.94 rows=104) (actual time=0.024..15.139 rows=2582 loops=3)
                                    -> Index lookup on users using X_cu_tenantid (tenantid=CV.tenant_id)  (cost=517.94 rows=2071) (actual time=0.019..11.862 rows=2859 loops=3)
                            -> Filter: (UEI.deleted = 0)  (cost=0.45 rows=1) (actual time=0.008..0.009 rows=1 loops=7747)
                                -> Index lookup on UEI using X_cuei_patient_id (patient_id=users.userid)  (cost=0.45 rows=1) (actual time=0.007..0.008 rows=1 loops=7747)
                        -> Filter: (UEI.identity_value = up.patient_id)  (cost=3.83 rows=4) (actual time=0.005..0.005 rows=0 loops=7747)
                            -> Index lookup on up using X_up_patient_id (patient_id=UEI.identity_value)  (cost=3.83 rows=4) (actual time=0.005..0.005 rows=0 loops=7747)
                    -> Covering index lookup on CV1 using U_tcv_tenant_configuration_id (tenant_id=CV.tenant_id)  (cost=0.44 rows=264) (actual time=0.015..0.094 rows=329loops=7747)
                -> Filter: (TC1.configuration_key in ('auxilary_physician_identifier_type','auxilary_nursing_agency_identifier_type'))  (cost=0.00 rows=1) (actual time=0.001..0.001 rows=0 loops=2547647)
                    -> Single-row index lookup on TC1 using PRIMARY (configuration_id=CV1.tenant_configuration_id)  (cost=0.00 rows=1) (actual time=0.001..0.001 rows=1 loops=2547647)
2

Hello @Sreeni,
A complete explanation is a bit beyond the scope of the volunteer nature of these forums. You can engage with our support department for complete explanation.
I will point out some big issues I see, most notably anywhere you see “loops” > 0. This means the query is doing separate dives/lookups for the parent values. Not good for performance. Also, I see two full table scans, one on a temporary table. I see many millions of rows also being joined with millions of other rows. This query is not optimal. I don’t see much index usage.