!!!!!! SELECT a.*,(a.p_price/a.p_area) AS price_sqm, c.p_location,c.description,c.imotbgtxt,c.complex_name,c.headpic_alt,c.vfile1,c.youtube_link,c.p_knowplace,c.gtrans, t4.ID as pl_ID,t4.type,t4.floor, t4.rn, t4.unit_name, t4.price as pricelist_price, t4.total_area as total_area_pricelist, t4.pricelist_price_sqm, t4.type_arr,c.prop_of_week,c.social_post_link,c.social_post_link2,c.social_post_link3 from properties a use index(`ad_type`,`order_by`) LEFT JOIN ( SELECT pricelist_tmp_table.* FROM ( SELECT tmp.ID,tmp.type,tmp.propID,tmp.type_arr, tmp.floor, tmp.apartment as unit_name, least( CASE WHEN tmp.price = 0 THEN 10000000 ELSE tmp.price END, CASE WHEN tmp.price1 = 0 THEN 10000000 ELSE tmp.price1 END, CASE WHEN tmp.price2 = 0 THEN 10000000 ELSE tmp.price2 END, CASE WHEN tmp.price3 = 0 THEN 10000000 ELSE tmp.price3 END, CASE WHEN tmp.price4 = 0 THEN 10000000 ELSE tmp.price4 END, CASE WHEN tmp.price5 = 0 THEN 10000000 ELSE tmp.price5 END, CASE WHEN tmp.price6 = 0 THEN 10000000 ELSE tmp.price6 END, CASE WHEN tmp.price7 = 0 THEN 10000000 ELSE tmp.price7 END, CASE WHEN tmp.price8 = 0 THEN 10000000 ELSE tmp.price8 END ) as price, tmp.total_area, (tmp.price/tmp.total_area) AS pricelist_price_sqm, @row_number:=CASE WHEN @propID = propID THEN @row_number + 1 ELSE 1 END AS rn, @propID := propID FROM properties_rows as tmp use index(`search_engine`) CROSS JOIN (select @row_number := 1) as x CROSS JOIN (select @propID := -1) as y WHERE CASE WHEN (type =6 OR type =7 OR type =14) THEN floor >=0 WHEN type!=5 THEN floor >= 9 ELSE floor >= 10 END AND apartment!='' AND status=0 and hide=0 AND type not in (14,15,16,17,26,46,59) AND type>4 ORDER BY propID, tmp.floor ASC ) as pricelist_tmp_table ) as t4 ON a.ID = t4.propID AND t4.rn <= 1000 ,properties_data c WHERE a.ad_hide='0' and a.ad_hide_BP='0' AND a.p_devid='470' AND a.publish=1 and a.p_cntrid='1' AND (YEAR( a.ad_pubdate ) >2018 OR ( YEAR( a.ad_pubdate ) <=2018 AND a.ad_status !=1 ) ) and a.ID=c.ID AND ((a.offplan=1 AND rn>0) OR a.offplan=0 ) AND c.lang='en' ORDER BY a.top DESC, a.ad_position ASC, a.ad_pubdate DESC, t4.rn ASC LIMIT 0,20