SQL query optimization taking lot of time in execution

Question

We have two tables one is properties and another one is property meta when we are getting data from one table "properties" , query only take less then one second in execution but when we are use join to get the data using bellow query from both tables its taking more then 5 second to fetch the data although we have only 12000 record in the tables , i think there is an issue in the sql query any help or suggestion will be appreciated.

SELECT 
        u.id, 
        u.property_title, 
        u.description, 
        u.city, 
        u.area,
        u.address, 
        u.slug, 
        u.latitude, 
        u.longitude, 
        u.sync_time, 
        u.add_date, 
        u.is_featured, 
        u.pre_construction, 
        u.move_in_date,
        u.property_status, 
        u.sale_price, 
        u.mls_number, 
        u.bedrooms, 
        u.bathrooms, 
        u.kitchens, 
        u.sub_area, 
        u.property_type, 
        u.main_image, 
        u.area_size as land_area, 
        pm7.meta_value as company_name, 
        pm8.meta_value as virtual_tour, 
        u.year_built, 
        u.garages 
    FROM 
        tbl_properties u 
            LEFT JOIN tbl_property_meta pm7 
                ON u.id = pm7.property_id 
            LEFT JOIN tbl_property_meta pm8 
                ON u.id = pm8.property_id 
    WHERE 
            u.status = 1 
        AND (pm7.meta_key = 'company_name') 
        AND (pm8.meta_key = 'virtual_tour') 
        AND (
                (
                        (   u.city = 'Delta' 
                        OR  u.post_code LIKE '%Delta%' 
                        OR  u.sub_area LIKE '%Delta%' 
                        OR  u.state LIKE '%Delta%') 
                    AND country = 'Canada'
                ) 
            OR  (
                        (   u.city = 'Metro Vancouver Regional District' 
                        OR  u.post_code LIKE '%Metro Vancouver Regional District%'
                        OR  u.sub_area LIKE '%Metro Vancouver Regional District%' 
                        OR  u.state LIKE '%Metro Vancouver Regional District%' )
                    AND country = 'Canada'
                )
            ) 
        AND 
            u.pre_construction ='0' 
    GROUP BY 
        u.id 
    ORDER BY 
        u.is_featured DESC, 
        u.add_date DESC

Answer 1

Try adding this compound index:

ALTER TABLE tbl_property_meta ADD INDEX id_key (property_id, meta_key);

If it doesn't help make things faster, try this one.

ALTER TABLE tbl_property_meta ADD INDEX key_id (meta_key, property_id);

And, you should know that column LIKE '%somevalue' (with a leading %) is a notorious performance antipattern, resistant to optimization via indexes. (There's a way to create indexes for that shape of filter in PostgreSQL, but not in MariaDB / MySQL.)