Uploaded image for project: 'Grouper'
  1. Grouper
  2. GRP-1319

Use database metadata to see if a table or view exists

    XMLWordPrintable

Details

    • Improvement
    • Status: Resolved
    • Minor
    • Resolution: Fixed
    • None
    • 2.3.1, 2.3.0.patch
    • API
    • None

    Description

      On Sat, May 28, 2016 at 03:28:41PM +0000, Hyzer, Chris wrote:
      >You did
      >
      >ANALYZE TABLE table_name for each table right?

      Yes, I performed that statemnent for each of the previously listed tables.
      In each case, the response was OK with no error.

      >Can you send me the output of this?
      >
      >EXPLAIN SELECT COUNT(1) FROM grouper_memberships_lw_v WHERE 1=0
      >
      >On my mysql it looks like this;
      >
      >1 PRIMARY \N \N \N \N \N \N \N Select tables optimized away
      >2 DERIVED gg ALL PRIMARY \N \N \N 597 Using temporary
      >2 DERIVED gs ref group_set_uniq_idx,group_set_gowner_field_idx,group_set_g
      owner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id group_se
      t_gowner_member_idx 123 grouper_v2_3.gg.id 6 Using where
      >2 DERIVED ms ref membership_uniq_idx,membership_member_cvia_idx,membership
      _enabled_idx,membership_member_idx,membership_member_list_idx,groupmem_ownid
      _fieldid_idx,fk_membership_field_id membership_uniq_idx 122 grouper_v2_3.gs.
      member_id 1 Using where
      >2 DERIVED gm eq_ref PRIMARY PRIMARY 122 grouper_v2_3.ms.member_id 1 Distin
      ct
      >2 DERIVED gfl eq_ref PRIMARY PRIMARY 122 grouper_v2_3.gs.field_id 1 Distin
      ct

      Previously below, I included the result of that command, although using
      "explain extended" and in csv format. Here are the results using just
      "explain" without the "extended" modifier in tab seperated fields output.
      The query took 1996 seconds to run.

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY NULL NULL NULL NULL NULL NULL NULL "Impossible WHERE"
      2 DERIVED gg ALL PRIMARY NULL NULL NULL 312562 "Using temporary"
      2 DERIVED gs ref group_set_uniq_idx,group_set_gowner_field_idx,group_set_go
      wner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id group_set
      _gowner_member_idx 43 grprtst.gg.id 3 "Using where"
      2 DERIVED ms ref membership_uniq_idx,membership_member_cvia_idx,membership_
      enabled_idx,membership_member_idx,membership_member_list_idx,fk_membership_f
      ield_id membership_uniq_idx 42 grprtst.gs.member_id 5 "Using where"
      2 DERIVED gm eq_ref PRIMARY PRIMARY 42 grprtst.ms.member_id 1 Distinct
      2 DERIVED gfl eq_ref PRIMARY PRIMARY 42 grprtst.gs.field_id 1 Distinct

      >
      >Thanks
      >Chris

      Aloha,
      -baron

      >----Original Message----
      >From: Baron Fujimoto baron@hawaii.edu
      >Sent: Friday, May 27, 2016 5:14 PM
      >To: Hyzer, Chris <mchyzer@isc.upenn.edu>
      >Subject: Re: [grouper-users] grouper registry upgrade 2.1.5 -> 2.2.2
      problems
      >
      >I used "show tables;" which resulted in the following (excluding the *_v
      >results, which I assume are views, which analyze fails on):
      >
      >grouper_attr_assign_action
      >grouper_attr_assign_action_set
      >grouper_attribute_assign
      >grouper_attribute_assign_value
      >grouper_attribute_def
      >grouper_attribute_def_name
      >grouper_attribute_def_name_set
      >grouper_attribute_def_scope
      >grouper_attributes
      >grouper_audit_entry
      >grouper_audit_type
      >grouper_change_log_consumer
      >grouper_change_log_entry
      >grouper_change_log_entry_temp
      >grouper_change_log_type
      >grouper_composites
      >grouper_ddl
      >grouper_ext_subj
      >grouper_ext_subj_attr
      >grouper_fields
      >grouper_group_set
      >grouper_groups
      >grouper_groups_types
      >grouper_loader_log
      >grouper_members
      >grouper_memberships
      >grouper_pit_attr_assn_actn
      >grouper_pit_attr_assn_actn_set
      >grouper_pit_attr_assn_value
      >grouper_pit_attr_def_name
      >grouper_pit_attr_def_name_set
      >grouper_pit_attribute_assign
      >grouper_pit_attribute_def
      >grouper_pit_fields
      >grouper_pit_group_set
      >grouper_pit_groups
      >grouper_pit_members
      >grouper_pit_memberships
      >grouper_pit_role_set
      >grouper_pit_stems
      >grouper_role_set
      >grouper_stems
      >grouper_types
      >subject
      >subjectattribute
      >
      >I think the only other db I can compare it with is the 2.2.2 instance (if
      that's
      >a valid comparison). I've attached the (sorted) results of queries for the
      table
      >indexes using "select table_name,index_name from
      information_schema.statistics"
      >
      >The "select count(1) from grouper_memberships_lw_v where 1=0" query
      finally
      >completed after ~28 min (I ran it with explain extended) with:
      >
      >id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extr
      a
      >1,PRIMARY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,"Impossible WHERE"
      >2,DERIVED,gg,ALL,PRIMARY,NULL,NULL,NULL,314725,100.00,"Using temporary"
      >2,DERIVED,gs,ref,"group_set_uniq_idx,group_set_gowner_field_idx,group_set_
      gowner_member_idx,fk_group_set_field_id,fk_group_set_member_field_id",group_
      set_gowner_member_idx,43,grprtst.gg.id,4,100.00,"Using where"
      >2,DERIVED,ms,ref,"membership_uniq_idx,membership_member_cvia_idx,membershi
      p_enabled_idx,membership_member_idx,membership_member_list_idx,fk_membership
      _field_id",membership_uniq_idx,42,grprtst.gs.member_id,2,100.00,"Using
      where"
      >2,DERIVED,gm,eq_ref,PRIMARY,PRIMARY,42,grprtst.ms.member_id,1,100.00,Disti
      nct
      >2,DERIVED,gfl,eq_ref,PRIMARY,PRIMARY,42,grprtst.gs.field_id,1,100.00,Disti
      nct
      >
      >-baron

      Attachments

        Activity

          People

            shilen.patel@at.internet2.edu Shilen Patel (duke.edu)
            shilen.patel@at.internet2.edu Shilen Patel (duke.edu)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist