MySQL Forums
Forum List  »  General

Posted by: Sreenivas Kolanthayappan
Date: March 21, 2023 09:58PM

Please help me understand the below execution plan

-> Sort row IDs: users.userid (actual time=24875.751..34999.822 rows=2547647 loops=1)
-> Table scan on <temporary> (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=2547647 loops=1)
-> Temporary table (cost=7093350.93..7093351.43 rows=6083517) (actual time=21616.863..24280.942 rows=2547647 loops=1)
-> Nested loop left join (cost=6484999.19 rows=6083517) (actual time=0.127..12408.895 rows=2547647 loops=1)
-> Nested loop left join (cost=336301.25 rows=1446307) (actual time=0.113..5024.664 rows=2547647 loops=1)
-> Nested loop left join (cost=188190.31 rows=1446307) (actual time=0.105..1015.506 rows=2547647 loops=1)
-> Nested loop left join (cost=41177.15 rows=5475) (actual time=0.083..85.897 rows=7747 loops=1)
-> Nested loop inner join (cost=38336.42 rows=5457) (actual time=0.069..43.167 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..1.780 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.036..1.725 rows=527 loops=1)
-> Filter: ((users.grp = 3) and (users.`status` <> 8)) (cost=517.94 rows=104) (actual time=0.022..13.537 rows=2582 loops=3)
-> Index lookup on users using X_cu_tenantid (tenantid=CV.tenant_id) (cost=517.94 rows=2071) (actual time=0.017..11.156 rows=2859 loops=3)
-> Filter: (UEI.deleted = 0) (cost=0.42 rows=1) (actual time=0.004..0.005 rows=1 loops=7747)
-> Index lookup on UEI using X_cuei_patient_id (patient_id=users.userid) (cost=0.42 rows=1) (actual time=0.004..0.005 rows=1 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.016..0.095 rows=329 loops=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)
-> Filter: (UEI.identity_value = up.patient_id) (cost=3.83 rows=4) (actual time=0.003..0.003 rows=0 loops=2547647)
-> Index lookup on up using X_up_patient_id (patient_id=UEI.identity_value) (cost=3.83 rows=4) (actual time=0.003..0.003 rows=0 loops=2547647)

Options: ReplyQuote

Written By
March 21, 2023 09:58PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.