Announcement

Announcement Module
Collapse
No announcement yet.

Using inner selects

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

  • Using inner selects

    Hello,

    After reading Peter's blog about a problem with inner selects not using indexes correctly, we had some questions about doing what was suggested (). We might have a mis-understanding of using his suggestion. Let me begin by laying out our structure and what we are trying to do. We have q02data (name value pair table), q02meta (meta for the data), and the sample table. I will not show the Sample table structure since it is huge. Just note that it has close to 150 columns. Below is a list of columns from the sample table that is indexed.

    INDEX_NAME ORDINAL_POSITION COLUMN_NAMEPRIMARY 1 INTV_KEYBTN_NNX_IDX 1 BTN_NNXBTN_NPA_IDX 1 BTN_NPACLLC_IDX 1 CLLCDCI_IDX 1 DCIDIVISION_NAME_IDX 1 DIVISION_NAMEERMS_RESP_EMAIL_IDX 1 ERMS_RESP_EMAILLB_AM_MODULE_IDX 1 LB_AM_MODULELB_SM_MODULE_IDX 1 LB_SM_MODULEMODULE_IDX 1 MODULEQTYPE_IDX 1 QTYPESALES_CODE_IDX 1 SALES_CODESTDYCODE_IDX 1 STDYCODETBD_NPA_IDX 1 TBD_NPAINTVDATE_IDX 1 INTVDATEFIRST_RADDM_IDX 1 FIRST_RADDMmetadatatable_idx 1 METADATATABLEstatus_idx 1 STATUScompcode_idx 1 COMPANY_CODE


    The q02data structure is :

    COLUMN_NAMEINTV_KEY varcharQLABEL varcharMULTI varcharRESPONSE varchar

    indexes include:

    INDEX_NAME ORDINAL_POSITION COLUMN_NAMEPRIMARY 1 INTV_KEYPRIMARY 2 QLABELPRIMARY 3 MULTIqlabel_idx 1 QLABELintv_key_idx 1 INTV_KEY


    The q02meta structure is :

    COLUMN_NAME TYPE_NAMEQUESTIONNAIRE_KEY bigintQUESTIONNAIRE textQLABEL varcharQTXT textQSEQUENCE bigintQABVTXT textANSWERVALUE varcharRTXT textRABVTXT textMEASUREMENT bigintRSEQUENCE bigintQSECTION textQSECTIONSEQUENCE bigintbirth datedeath datesegment varchar

    indexes include

    INDEX_NAME ORDINAL_POSITION COLUMN_NAMEqsection_idx 1 QSECTIONqlabel_idx 1 QLABELanswervalue_idx 1 ANSWERVALUErabvtxt_idx 1 RABVTXT


    Notice we have double indexes with the primary key column. And notice that the primary key is two colums in the q02data table. If we only need to access one column shouldn't we need to index that column seperatly? (First Question

    Here is our query:

    select response, interviewDate, meta.measurementfrom q02meta as metaleft outer join ( select response, st.intvdate as interviewDate, data.qlabel from q02data as data inner join sample st on st.INTV_KEY = data.INTV_KEY where st.intvdate >= '2007-01-01' and st.intvdate <= '2007-01-31' and data.qlabel = 'OV1' and st.status = '001' order by st.intvdate asc ) as answer on meta.qlabel = answer.qlabelwhere meta.ANSWERVALUE = answer.response


    Here is the explain

    id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY ALL 2623 1 PRIMARY meta ref qlabel_idx,answervalue_idx qlabel_idx 53 answer.qlabel 10 Using where2 DERIVED data ref qlabel_idx,intv_key_idx qlabel_idx 102 66224 Using where; Using temporary; Using filesort2 DERIVED st ref PRIMARY,INTVDATE_IDX,status_idx PRIMARY 29 test.data.INTV_KEY 1 Using where


    Now here is our query using a view

    create view q02mycee asselect data.intv_key as interviewKey, response, data.qlabel, st.*from q02data as datainner join sample st on st.INTV_KEY = data.INTV_KEY where data.qlabel = 'OV1' and st.status = '001'order by st.intvdate ascselect response, intvdate as interviewDate, meta.measurementfrom q02mycee datainner join q02meta meta on meta.qlabel = data.qlabelwhere intvdate >= '2007-01-01' and intvdate <= '2007-01-31' and meta.answervalue = data.response

    And here is the explain:

    id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY ALL 67162 Using where1 PRIMARY meta ref qlabel_idx,answervalue_idx qlabel_idx 53 data.qlabel 10 Using where2 DERIVED data ref PRIMARY,qlabel_idx,intv_key_idx qlabel_idx 102 66224 Using where; Using temporary; Using filesort2 DERIVED st ref PRIMARY,status_idx PRIMARY 29 test.data.INTV_KEY 1 Using where


    We believe that the index should have been used with the view just as the suggestion in Peter's blog. Any help on this would be appreciated.

    Thanks,
    Calvin
Working...
X