-
select and index help
I have long sql select , the detail is like, could any one give some general ideals on how to improve it???
Code:
explain select table2.UniversityN, CONCAT('N-', FeeType) as flag, StudentVisa, CONCAT('N*', Curriculumlist)as flag2 from table2, table3, table1 where table2.UserId=table3.UserId and table2.UserId=table1.UserId and CONCAT(table3.UserId, left (FeeType, 8)) not in( select CONCAT(table3.UserId, left (FeeType, 8))from table2, table3, table1 where table2.UserId=table3.UserId and table2.UserId=table1.UserId group by table3.UserId, DueDate, ProcessStatus, ApprovalDate,DefferredPayDate, left (FeeType, 8), StudentVisa, table2.UniversityN having count(UpdateId) >1 ) and processStatus ='Pending approval'and StudentVisa !='No' and UpdateId like 'U%' and Curriculumlist !='Other' and FeeType in('typeOne') and ReceiveDate between '2010-01-01' and '2010-12-31' group by FeeType, StudentVisa,Curriculumlist,table2.UniversityN
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>id</th><th>select_type</th><th>table</th><th>type</th><th>possible_keys</th><th>key</th><th>key_len</th><th>ref</th><th>rows</th><th>Extra</th></tr>
<tr>
<td>1</td><td>PRIMARY</td><td>table3</td><td>ref</td><td>UserId,idx_FeeType,idx_ProcessStatus</td><td>idx_ProcessStatus</td><td>138</td><td>const</td><td>4</td><td>Using where; Using temporary; Using filesort</td></tr>
<tr>
<td>1</td><td>PRIMARY</td><td>table1</td><td>eq_ref</td><td>PRIMARY,idx_StudentVisa,idx_CurriculumList</td><td>PRIMARY</td><td>4</td><td>student.table3.UserId</td><td>1</td><td>Using where</td></tr>
<tr>
<td>1</td><td>PRIMARY</td><td>table2</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>student.table3.UserId</td><td>1</td><td>Using where</td></tr>
<tr>
<td>2</td><td>DEPENDENT SUBQUERY</td><td>table1</td><td>index</td><td>PRIMARY</td><td>idx_StudentVisa</td><td>18</td><td></td><td>2354</td><td>Using index; Using temporary; Using filesort</td></tr>
<tr>
<td>2</td><td>DEPENDENT SUBQUERY</td><td>table2</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>student.table1.UserId</td><td>1</td><td>(null)</td></tr>
<tr>
<td>2</td><td>DEPENDENT SUBQUERY</td><td>table3</td><td>ref</td><td>UserId</td><td>UserId</td><td>4</td><td>student.table2.UserId</td><td>1</td><td>Using where</td></tr>
</table>
