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.

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)

Hi,

Can you provide explain plan of both above queries(i.e explain select *.) and table structure (show create table)?

The problem has been solved. It deals with importing views from a mysqldump. The views will take forever to query if loaded into the db from a dump, and must be dropped from the dump file and then recreated manually after the dump has been imported with no views. The bug has been found on the mentioned version of Percona as well as other mysql-based databases, such as Amazon RDS.
Thanks again