fetch data from two tables I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property which amenities are kitchen and bedroom and Breakfast and internet etc. My created Query is like:

SELECT 
    property.*,
    property_type.property_type 
FROM property,property_type,apartment_amenity 
WHERE property.approval='Y' 
    AND property.propertytype = property_type.property_type_id 
    AND property.country like '%india%' 
    AND (property.city like '%indore%' OR property.state like '%madhya pradesh%') 
    AND property.Maximum_Capacity &gt;=1 
    AND property.pid = apartment_amenity.property_id 
    AND apartment_amenity.amenity_id in (1,2,3) 
ORDER by is_online_bookable DESC 
LIMIT 0,10;
What you need to do is refactor the query as follows:

Subquery #1 : Collect all the property_id values that have all 3 amenities.

SELECT property_id,COUNT(1) amenity_count
FROM apartment_amenity
WHERE amenity_id IN (1,2,3)
GROUP BY property_id
HAVING COUNT(1) = 3;


Subquery #2 : Collect pid and propertytype values from property table by address

SELECT property_id FROM property
WHERE approval='Y'
AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity &gt;=1;


Subquery #3: Join Subquery #1 and Subquery #2

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity &gt;=1) B


Impose ORDER BY and LIMIT 10 on Subquery #3

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity &gt;=1) B
ORDER BY is_online_bookable LIMIT 10


Join Subquery #3 to property and property_type tables

SELECT PR.*,PT.property_type
FROM
(
    SELECT B.pid,B.propertytype FROM
    (SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
    WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
    INNER JOIN
    (SELECT pid,propertytype FROM property
    WHERE approval='Y' AND country like '%india%'
    AND (city like '%indore%' OR state like '%madhya pradesh%')
    AND Maximum_Capacity &gt;=1) B
    ORDER BY is_online_bookable LIMIT 10
) K
INNER JOIN property PR      ON K.pid = P.pid
INNER JOIN property_type PT ON K.propertytype = PT.property_type_id;


CAVEAT #1

You have to rebuild Subquery #1 every time you get a new set of amenities and make sure the HAVING COUNT(1) = match the sum of the amenities selected.

CAVEAT #2

Subquery #2 is going to have the worst performance because of


using LIKE
approval='Y' (Cardinality of 2, Y and N values)
Maximum_Capacity &gt;=1 would just be a full scan of all properties


A compound index of (approval,Maximum_Capacity) would be useless

SUGGESTION

Change Subquery #2 from

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity &gt;=1) B


to

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country = 'india'
AND (city like 'indore' OR state like 'madhya pradesh')
AND Maximum_Capacity &gt;=1) B


You could then create a compound index on (country,city,state)

You could also create a FULLTEXT index and change Subquery #2 to do FULLTEXT searching

There are other things you could change for Subquery #2. The main point I am making ? Optimize Subquery #2 as much as you can for the best performance.

EPILOGUE

I have suggested refactoring queries before


May 16, 2011 : Fetching a Single Row from Join Table (StackOverflow)
May 21, 2011 : How can I optimize this MySQL query further?
Jun 24, 2011 : What is more efficient, a where clause or a join with million plus row tables?
Dec 04, 2012 : How do I terminate a running SELECT query without restarting MySQL?