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 ([URL=“http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/”][/URL]). 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