Speed up the query when using multiple joins in mysql

Question

I have sql query where need to join 5 tables. I have tried this query so far. It is working but taking long time. what can be done here to optimise following query?

$select_query = 'select ';

        $select_query .= "ROUND((
                             6371 * ACOS(
                               COS(RADIANS('.$lat.'))  COS(RADIANS(lat))  COS(
                                 RADIANS(lng) - RADIANS('.$lng.')
                               ) + SIN(RADIANS('.$lat.')) * SIN(RADIANS(lat))
                             )
                           ),2) AS property_distance ,";

        $select_query .= "
                        pro.id as id,
                        pro.user_id,
                        pro.category_id,
                        pro.total_price,
                        pro.size,
                        pro.lat,
                        pro.lng,
                        pro.city,
                        pro.city_english,
                        pro.created_at,
                        pimg.image as property_images,
                        pimg.property_id,
                        pa.property_id,
                        pa.category_attribute_id,
                        pa.is_multiple_data,
                        pa.attribute_value,

                        ca.category_id,
                        ca.attribute_name,
                        ct.category_id,
                        ct.category_name,

                        cat.attribute_id,
                        cat.attribute_label,
                        cat.locale

                     FROM
                        property pro FORCE INDEX (property_index)
                    left join property_images pimg on pro.id=pimg.property_id

                    JOIN property_attributes pa ON
                        pa.property_id = pro.id

                    left JOIN category_attributes ca ON
                        ca.id = pa.category_attribute_id

                    left JOIN category_attributes_translations cat ON
                        ca.id = cat.attribute_id

                    left JOIN categories_translation ct ON
                        pro.category_id = ct.category_id

                    WHERE pro.is_confirm='1' and pro.status='1' and pro.deal_finish='0' and cat.locale='" . $locale . "' and ct.locale='" . $locale . "'

                    GROUP BY pro.id HAVING property_distance<=10 ORDER by pro.id DESC";

Then finally running this query.

Please suggest me the proper way to optimise this query.

Answer 1

I would modify the query to rip through the property table and eliminate as many rows as possible, before doing any joins. The condition in the HAVING clause on the result of the Great Circle Distance calculation is going to require that calculation to be done on every row that isn't otherwise eliminated by the WHERE clause.

I would write a query against just the property table, something like this:

         SELECT ROUND( ( 6371 * ACOS( COS(RADIANS( :lat ) ) 
                                    * COS(RADIANS(pro.lat))
                                    * COS(RADIANS(pro.lng) - RADIANS( :lng ) ) 
                                    + SIN(RADIANS( :lat ))
                                    * SIN(RADIANS(pro.lat))
                                )
                       )
                ,2) AS property_distance
              , pro.id
              , pro.user_id
              , pro.category_id
              , pro.total_price
              , pro.size
              , pro.lat
              , pro.lng
              , pro.city
              , pro.city_english
              , pro.created_at
           FROM property pro
          WHERE pro.is_confirm  = '1' 
            AND pro.status      = '1'
            AND pro.deal_finish = '0'
         HAVING property_distance <= 10
          ORDER
             BY pro.id DESC

This assumes that id is unique in property table. This should be able to make effective use of index that has leading columns deal_finish,status and is_confirm to eliminate some rows from being considered.

   ... ON property (deal_finish,status,is_confirm,...)

Once we have that set, we can reference it as an inline view (derived table) in an outer query, and the outer query can do joins to other tables.

Note that if we have multiple matching rows in product_images and in product_attributes, doing joins to both tables will create a semi-Cartesian product, with each row from product_image matched with each row from product_attribute. i.e. 20 rows from product_image cross joined to 20 rows from product_attribute will generate a set of 400 rows.

SELECT c.*
  FROM (  -- inline view query
          SELECT ROUND( ( 6371 * ACOS( COS(RADIANS( :lat ) ) 
                                    * COS(RADIANS(pro.lat))
                                    * COS(RADIANS(pro.lng) - RADIANS( :lng ) ) 
                                    + SIN(RADIANS( :lat ))
                                    * SIN(RADIANS(pro.lat))
                                )
                       )
                ,2) AS property_distance
              , pro.id
              , pro.user_id
              , pro.category_id
              , pro.total_price
              , pro.size
              , pro.lat
              , pro.lng
              , pro.city
              , pro.city_english
              , pro.created_at
           FROM property pro
          WHERE pro.is_confirm  = '1' 
            AND pro.status      = '1'
            AND pro.deal_finish = '0'
         HAVING property_distance <= 10
          ORDER BY pro.id DESC
       ) c

  LEFT
  JOIN product_images pimg 
    ON pimg.product_id = c.id

 ORDER BY c.id DESC

And now there's the whole issue of using GROUP BY to collapse rows, and expressions that are not functionally depend, beahvior with ONLY_FULL_GROUP_BY sql_mode, et al.