# Interpreting mysql EXPLAIN ANALYZE

**URL:** <https://forums.percona.com/t/interpreting-mysql-explain-analyze/20675>\
**Category:** Other MySQL® Questions\
**Created:** [March 17, 2023, 5:17am UTC](https://forums.percona.com/t/interpreting-mysql-explain-analyze/20675 "2023-03-17T05:17:07Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sreeni](https://avatars.discourse-cdn.com/v4/letter/s/dc4da7/32.png) [@Sreeni](https://forums.percona.com/u/Sreeni)\
**Post date:** [March 17, 2023, 5:17am UTC](https://forums.percona.com/t/interpreting-mysql-explain-analyze/20675/1 "2023-03-17T05:17:07Z")

</div>

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

```auto
 -> 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)

```

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [March 17, 2023, 2:43pm UTC](https://forums.percona.com/t/interpreting-mysql-explain-analyze/20675/2 "2023-03-17T14:43:27Z")

</div>

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.
