SQL--Dynamic SQL optimization

I wrote the following SQL

        select        tall. Location_id,        tall. Location_name,        tall. Job_id,        tall. Job_name,        tall. Negotiable_flag,        tall. Salary_max,        tall. Salary_min,        tall. Add_time,        tall. Enterprise_id,        tall. Enterprise_name,        tall. Enterprise_logo,        tall. Work_years_id,        tall. Work_years_info,        ifnull (&NBSP;TUJF. focus_flag,0)  as flag        from         ( Select        tj. Job_id,        tj. Location_id,        tl. Location_name,        tj. Job_name,        tj. Negotiable_flag,        tj. Salary_max,        tj. Salary_min,        tj. Add_time,        tj. Enterprise_id,        te. Enterprise_name,        te. Enterprise_logo,        twy. Work_years_id,        twy. work_years_info        from &Nbsp;      t_job        tj,t_location  tl,t_enterprise te,t_hr th,t_work_years        twy,t_ job_category tjc,t_job_type tjt,t_salary ts         Where tj. Location_id        = tl. Location_id        and tl. Location_level = 2        and tl. use_flag = 1        and         tj. Enterprise_id = te. Enterprise_id        and te. use_flag = 1        and tj.hr_id =         th.hr_id        and th.Use_flag = 1        and tj. Work_years_id = twy. Work_years_id        and tj. JOB_CATEGORY_ID&NBSP;=&NBSP;TJC. JOB_CATEGORY_ID&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;AND&NBSP;TJC. use_flag = 1        and         tj. Job_type_id = tjt. Job_type_id        and tjt. Use_flag = 1        and ts. salary_id =        #{salaryid}         and ts. Salary_max &gt;=tj. Salary_max        and ts. Salary_min &lt;=        tj. Salary_min        <if test= "JobCategoryId ! = null ">            AND    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TJC. job_category_id = #{jobcategoryid}        </if>         <if test= "Locationid != null" >             AND             tl. location_id = #{locationid}        </if>         <if test= "Workyearid != null" >             AND             twy. work_years_id = #{workyearid}        </if>      &nbSp;  <if test= "Jobtypeid != null" >             and            tjt. job_type_id = #{jobtypeid}        </if>         <if test= "Lasttime != null" >             AND             tj. add_time &lt;= #{lasttime}        </if>         group by        tj. job_id        order by         tj. ADD_TIME&NBSP;DESC)  tall        left join   & NBsp;     (select job_id        as         focus_job_id, ' 1 '  as FOCUS_FLAG from         t_user_job_focus where        user_id  =        #{userId} and TYPE =  ' ")   Tujf        on        tall. JOB_ID&NBSP;=&NBSP;TUJF. focus_job_id        limit         #{pageindex}

Optimized SQL is as follows

        SELECT         talltwo.job_id,        talltwo.location_id,         talltwo.location_name,        talltwo.job_name ,        talltwo.negotiable_flag,         tallTwo.SALARY_MAX,        tallTwo.SALARY_MIN,         tallTwo.ADD_TIME,         talltwo.enterprise_id,        talltwo.enterprise_name,         tallTwo.ENTERPRISE_LOGO,         talltwo.work_years_id,        talltwo.work_years_info,         talltwo.hr_id,        talltwo.job_category_id,         ifnull (&NBSP;TUJF. focus_flag,0)  as flag        FROM          (Select        tall. Job_id,        tall. Location_id,        tl. Location_name,        tall. Job_name,        tall. Negotiable_flag,        tall. Salary_max,        tall. Salary_min,        tall. Add_time,        tall. Enterprise_id,        te. Enterprise_name,        te. Enterprise_logo,&nbSp;       tall. Work_years_id,        twy. work_years_info,        tall.hr_id,         tall. Job_category_id,        tall. job_type_id        from          (        SELECT         Tj. Job_id,        tj. Location_id,        tj. Job_name,        tj. Negotiable_flag,        tj. Salary_max,        tj. Salary_min,        tj. Add_time,        tj. enterprise_id,        tj. work_years_id,        tj.hr_id,         tj. Job_category_id,        tj. job_type_id        from         t_job tj        <if test= "salaryId !=  Null ">            ,t_salary ts         </if>        WHERE         1 = 1        < If test= "Salaryid != null" >             and ts. Salary_id = #{salaryid}            and  ts. SAlary_max            &gt;=tj. Salary_max            and ts. Salary_min &lt;= tj. salary_min        </if>         <if test= "Locationid != null" >             and            tj. location_id = #{locationid}        </if>         <if test= "Workyearid != null" >             AND             tj. work_years_id = #{workyearid}        </if>         <if test= "Jobcategoryid != null" >             AND             tj. Job_category_id =            #{jobcategoryid}         </if>         <if test= "Jobtypeid != null" >             and            tj. job_type_id = #{jobtypeid}        </if>         <if test= "Lasttime != null" >             AND             tj. Add_time &lt;= #{lasttime}        </if>         group by tj. Job_id        order by tj. add_time desc        )  tall         left join t_location        tl on  tall. Location_id = tl. Location_id        and tl. location_level = 2        and         tl. Use_flag = 1        left join t_enterprise te  on tall. Enterprise_id =        te. Enterprise_id        and te. Use_flag = 1        left join t_hr th on tall.hr_id =         th.hr_id        and th. use_flag = 1        left join t_work_years  Twy on        tall. Work_years_id = twy. work_years_id        left join t_job_category tjc  On        tall. JOB_CATEGORY_ID&NBSP;=&NBSP;TJC. JOB_CATEGORY_ID&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;AND&NBSP;TJC. Use_flag = 1        left join t_job_type tjt  on        tall. Job_type_id = tjt. Job_type_id        and tjt. USE_FLAG&NBSP;=&NBSP;1) &NBSP;&NBSP;&NBSP;&Nbsp;    talltwo        left join  (SELECT  job_id as focus_job_id, ' 1 '  as FOCUS_FLAG from         t_user_job_focus where user_id = #{userid} and type =   ' ")  tujf        ON       &NBSP;&NBSP;TALLTWO.JOB_ID&NBSP;=&NBSP;TUJF. Focus_job_id        limit #{pageindex}

Comparison of Ideas

My idea: First correlate all the required tables to find out all the data, then filter according to the conditions.

Project Manager ideas: First find out the basic data, filter according to the conditions, and then associated with the required table.

SQL--Dynamic SQL optimization