Not the answer you need?
Register and ask your own question!

Table vs View query

radonradon EntrantInactive User Role Beginner
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

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.
  • radonradon Entrant Inactive User Role Beginner
    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)
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.