Use database metadata to see if a table or view exists

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