Table vs View query

[radon]

I have set up a 3 node cluster, and imported a mysql db successfully. When attempting to query a view using “select * from view_name”, there is an incredible lag time, 100 records takes about 10 seconds. However when querying tables, Percona works as expected. Does anyone know why this is happening or have a workaround for it? Thanks in advance!

-Tom

[niljoshi]

Hi,

Can you provide explain plan of both the queries? (select queries for view and table). It would be helpful if you can provide table/view structures and version of XtraDB cluster.

[radon]

Thanks for the reply. I’m using 5.5.27-55 Percona XtraDB Cluster (GPL) running on 32-bit Ubuntu 11.10. Running ‘select * from crs_client_search’ takes about an hour for 15k rows but interestingly, when running the query for the view as mentioned below, it runs as expected in a reasonable amount of time. It seems as though the definer algorithm to produce the view from the appropriate tables in the db takes an incredibly long time?
The sql for the ‘crs_client_search’ view is about 2 pages long:
select p.p_uuid AS p_uuid,p.given_name AS given_name,p.custom_name AS custom_name,p.family_name AS family_name,p.full_name AS full_name,p.incident_id AS incident_id,pd.birth_date AS birth_date,pd.years_old AS years_old,pd.opt_country AS opt_country,pd.occupation AS occupation,pd.opt_gender AS opt_gender,ps.opt_status AS opt_status,ps.last_updated AS last_updated,ps.creation_time AS creation_time,c1.contact_value AS home_phone,c2.contact_value AS mobile_phone,c3.contact_value AS alt_phone,c4.contact_value AS email,c5.contact_value AS street_1,c6.contact_value AS street_2,c7.contact_value AS city,c8.contact_value AS state,c9.contact_value AS postal,ptf.in_date AS in_date,(case when (ps.opt_status = ‘in’) then NULL else ptf.out_date end) AS out_date,(case when (ps.opt_status = ‘trn’) then NULL else ptf.dest_facility end) AS dest_facility,f.facility_uuid AS facility_uuid,f.facility_name AS facility_name,f.facility_code AS facility_code,f.facility_group AS facility_group,sl1.language AS altlang1,sl2.language AS altlang2,sl3.language AS altlang3,ec.name AS ec_name,ec.phone AS ec_phone,ec.relationship AS ec_relation,ma.injury AS injury,ma.special_medical AS special_medical,ma.special_mental AS special_mental,ifnull(gd.adults,1) AS adults,ifnull(gd.children,0) AS children,ifnull(gd.infants,0) AS infants,ifnull(gd.unknown,0) AS unknown,ifnull(ptg.p_uuid_2,‘’) AS group_primary,((gd.adults + gd.children) + gd.infants) AS group_count,cr.relation AS relation,ifnull(p2.full_name,‘’) AS client_group_name from (((((((((((((((((((((((((person_uuid p join crs_person_to_group ptg on((p.p_uuid = ptg.p_uuid_1))) left join crs_common_relations cr on((ptg.relation = cr.id))) left join person_uuid p2 on((ptg.p_uuid_2 = p2.p_uuid))) left join person_details pd on((p.p_uuid = pd.p_uuid))) left join person_status ps on((p.p_uuid = ps.p_uuid))) left join contact c1 on(((c1.p_uuid = p.p_uuid) and (c1.opt_contact_type = ‘curr’)))) left join contact c2 on(((c2.p_uuid = p.p_uuid) and (c2.opt_contact_type = ‘cmob’)))) left join contact c3 on(((c3.p_uuid = p.p_uuid) and (c3.opt_contact_type = ‘calt’)))) left join contact c4 on(((c4.p_uuid = p.p_uuid) and (c4.opt_contact_type = ‘email’)))) left join contact c5 on(((c5.p_uuid = p.p_uuid) and (c5.opt_contact_type = ‘street_1’)))) left join contact c6 on(((c6.p_uuid = p.p_uuid) and (c6.opt_contact_type = ‘street_2’)))) left join contact c7 on(((c7.p_uuid = p.p_uuid) and (c7.opt_contact_type = ‘city’)))) left join contact c8 on(((c8.p_uuid = p.p_uuid) and (c8.opt_contact_type = ‘state’)))) left join contact c9 on(((c9.p_uuid = p.p_uuid) and (c9.opt_contact_type = ‘postal’)))) left join crs_latest_ptf ptf on((p.p_uuid = ptf.p_uuid))) left join fms_facility f on((f.facility_uuid = ptf.facility_uuid))) left join crs_person_to_language pl1 on(((p.p_uuid = pl1.p_uuid) and (pl1.ordinal = 1)))) left join crs_language sl1 on((pl1.language = sl1.id))) left join crs_person_to_language pl2 on(((p.p_uuid = pl2.p_uuid) and (pl2.ordinal = 2)))) left join crs_language sl2 on((pl2.language = sl2.id))) left join crs_person_to_language pl3 on(((p.p_uuid = pl3.p_uuid) and (pl3.ordinal = 3)))) left join crs_language sl3 on((pl3.language = sl3.id))) left join crs_emerg_cont ec on((p.p_uuid = ec.p_uuid))) left join crs_med_alert ma on((p.p_uuid = ma.p_uuid))) left join crs_group_details gd on((p.p_uuid = gd.p_uuid))) where (ps.isdeleted = 0)