i using where exists in mysql query taking time execute. can guys tell me how can speed query or suggest me alternate query.
here query
select a.property_id, count( a.booking_id ) total_bookings, group_concat( concat( date_format( a.check_in, '%m/%d/%y' ) , ' ', date_format( a.check_out, '%m/%d/%y' ) , ' ', '(', ( case a.book_status when 1 'pending' when 3 'confirm' end ), ')', ' | <a href=\'manage_booking.php?action=ed&id=', a.booking_id, '\' target=_blank class=linkun >view detail</a>', '<br>' ) ) dates apl_property_booking exists ( select booking_id apl_property_booking b b.booking_id != a.booking_id , b.property_id = a.property_id , ( b.check_in < a.check_out , a.check_in < b.check_out ) , b.book_status in ( 1, 3 ) , ( '2015-04-01' <= date_add( b.check_out, interval -1 day ) , '2015-04-27' >= date_add( b.check_in, interval +1 day ) ) ) , a.book_status in ( 1, 3 ) , ( '2015-04-01' <= date_add( a.check_out, interval -1 day ) , '2015-04-27' >= date_add( a.check_in, interval +1 day ) ) group a.property_id order a.check_in desc limit 0 , 30
i solved removing mysql where exists , replacing left join. query where exists taking "37.4117 sec" execute while query left join taking "0.0097 sec" execugte
here new query
select a.property_id, count( a.booking_id ) total_bookings, group_concat( concat( date_format( a.check_in, '%m/%d/%y' ) , ' ', date_format( a.check_out, '%m/%d/%y' ) , ' ', '(', ( case a.book_status when 1 'pending' when 3 'confirm' end ), ')', ' | <a href=\'manage_booking.php?action=ed&id=', a.booking_id, '\' target=_blank class=linkun >view detail</a>', '<br>' ) ) dates apl_property_booking left join apl_property_booking b on b.property_id = a.property_id b.booking_id != a.booking_id , (b.check_in < a.check_out , a.check_in < b.check_out ) , b.book_status in ( 1, 3 ) , ( '2015-04-01' <= date_add( b.check_out, interval -1 day ) , '2015-04-27' >= date_add( b.check_in, interval +1 day ) ) , a.book_status in ( 1, 3 ) , ( '2015-04-01' <= date_add( a.check_out, interval -1 day ) , '2015-04-27' >= date_add( a.check_in, interval +1 day ) ) group a.property_id order a.check_in desc limit 0 , 30 