Announcement

Announcement Module
Collapse
No announcement yet.

Table vs View query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Table vs View query

    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

  • #2
    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.

    Comment


    • #3
      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)

      Comment


      • #4
        Hi,

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

        Comment


        • #5
          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

          Comment

          Working...
          X