2

I've a table "location" with the structure:

id  | property_id | location_type
1   | 1           | 1
2   | 1           | 2
3   | 2           | 1
4   | 3           | 2
5   | 4           | 1
6   | 4           | 2

I've another table "amenities" with the structure:

id  | property_id | amenity_type
1   | 1           | 1
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 1
6   | 4           | 3

I've another table "property" with the structure:

id  | property_id | property_type
1   | 1           | 2
2   | 1           | 3
3   | 2           | 2
4   | 3           | 4
5   | 4           | 2
6   | 4           | 3

id - is the primary key of the respective table. property_id is the property ID of my database (foreign key). location_type is beach (value - 1), mountain (value - 2).

amenity_type is car (value - 1), bike (value - 2), football (value - 3).

property_type is villa (value - 2), house (value - 3)

Can you please help me in getting the SQL query to select the property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1 i.e. a property has beach and mountains and car and villa and house.

This is just an example of a filter in my property search application. There can be n combinations for this. Please share a common logic which will join all these tables and be optimized to search for around a million records.

I also need count for all the conditions. Please share query for the same.

[edit for taking count]:

suppose I get the count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) as 1500. I need to get the count with same condition and other property_type, location_type, amenity_type.

For example:

1) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 3

2) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = 4

3) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 2

4) count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND amenity_type = 3

and so on. Its getting a big overhead for me. Please help. Also, please note that location_types, amenity_type, property_type are dynamic i.e. a user can add more location_type in master tables and I need to get the count for any more location_types.

Improve this question
3
  • Can you combine all three tables into a single table? It would ease the performance penalty of the JOINs. Commented Aug 13, 2011 at 22:21
  • Actually, it was a single table initially. It had values like: id | property_id | location_type | amenity_type | property_type 1 | 1 | 1,2,3 | 2,3 | 1,2. But it was getting difficult for me to extract property_id with location_type 1 and 3 and amenity type as 2. Commented Aug 13, 2011 at 22:23
  • You can make the columns to be of type int - it would boost the performance when having an index. Commented Aug 13, 2011 at 22:27

3 Answers 3

Reset to default
4

there's nothing wrong with multiple tables in a case like this where you have multiple values. what you are doing here is fine. here's the query you need:

select distinct l1.property_id                                                       
  from location as l1, location as l2,                                      
       amentities as a,                                                     
       properties as p1, properties as p2                                   
 where l1.property_id = l2.property_id                                      
   and l1.property_id = a.property_id                                       
   and l1.property_id = p1.property_id                                      
   and l1.property_id = p2.property_id                                      
   and l1.location_type = 1                                                 
   and l2.location_type = 2                                                 
   and a.amenity_type = 1                                                   
   and p1.property_type = 3                                                 
   and p2.property_type = 1

it's easy to write once you see how:

  1. create an alias for each table/condition combination you need
  2. make sure that all address the same property_id at the same time (l1.property_id = ...)
  3. then specify the condition for each table/condition

you could also use "join" explicitly, but i find the approach above simpler and it shouldn't matter to the db engine.

[Edit from ypercube showing the JOIN syntax]:

SELECT p.id  
FROM 
    property AS p           
  JOIN
    location AS l1
        ON  l1.property_id = p.id  
        AND l1.location_type = 1 
  JOIN
    location AS l2
        ON  l2.property_id = p.id  
        AND l2.location_type = 2 
  JOIN                      
    amentities AS a1
        ON  a1.property_id = p.id
        AND a1.amenity_type = 2                 
  JOIN
    properties AS p1
        ON  p1.property_id = p.id  
        AND p1.property_type = 3 
  JOIN
    properties AS p2 
        ON  p2.property_id = p.id  
        AND p2.property_type = 1

[comment from ac: this and the initial syntax should be translated internally into the same query, so both are equally efficient]

[edit about performance] in general, the only (or at least, by far the most important) thing you need to worry about for good database performance is indices. you want to declare an index on the property_id column of every table, and also on the different type columns you have. that is critical. but once you have that, for just a few million rows, this should be fast - the above is not a very complex query and you have less than a GB of data (consider using tinyint for the type columns). don't worry... and the aliases (the "as X") are not an issue at all.

[edit for counts] for count of (property_id with location_type = 1 AND location_type = 2 AND amenity_type = 1 AND property_type = 3 AND property_type = 1) AND location_type = X you want something like:

select lx.location_id, count(l1.property_id)
  from location as l1, location as l2, location as lx
       amentities as a,
       properties as p1, properties as p2
 where l1.property_id = l2.property_id
   and l1.property_id = a.property_id
   and l1.property_id = p1.property_id
   and l1.property_id = p2.property_id
   and l1.property_id = lx.property_id
   and l1.location_type = 1
   and l2.location_type = 2
   and a.amenity_type = 1
   and p1.property_type = 3
   and p2.property_type = 1
 group by lx.location_type

but i haven't tested it or anything. that should give you multiple rows, with the location_type and the count for each row (so you do all the queries you gave above in one).

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Thanks! I've around 15 different location_type, 12 amenities and 10 property_types. Can you help me with the query to resolve different combinations. This would be for almost a million properties.
i suspect you can do it yourself. it's not that complicated if you just follow the pattern above and generate the query in your code. just collect together the conditions, then loop through them generating the query piece by piece.
Thanks Andrew, I can do that. Just curious if declaring multiple aliases will make DB engine slow? Is this the only way to do it and get good performance?
+1. Nice solution. I'd like to test your query and mine with a large dataset in order to see performance but I suspect your query will run faster than mine. :)
i just added "distinct" btw. i am not completely sure it is needed, but it will help if you have messy input (like the same condition specified twice).
|
1 
select property_id from (
select property_id
from location
where location_type in (1,2)
group by property_id
having count(location_type) = 2
union all
select property_id
from amenities
where amenity_type = 1
group by property_id
union all 
select property_id
from property
where property_type in (1,3)
group by property_id
having count(property_type) = 2
) as t
group by property_id
having count(property_id) = 3

Following the same logic of my previous answer you can use union all in order to find property_id that satisfy each condition. In this case there are 3 queries. So you can group on this property and if the count is equal to 3, it means that the property_id satisfies all criteria. If even a single criterium is not satisfied, the property_id will not be returned.

edit.

Another possible solution:

select property_id
from location
where location_type in (1,2)
group by property_id
having count(location_type) = 2
and property_id in (
select property_id
from amenities
where amenity_type = 1
group by property_id )
and property_id in (
select property_id
from property
where property_type in (1,3)
group by property_id
having count(property_type) = 2
)

it works too with your few records of example but I'm sure that on large dataset this query would have very poor performance. ;)

Improve this answer

2 Comments

Thanks Nick. Will try to make use of your logic. Would come back to you in case I face any trouble. Thanks for all the help! ;)
Nick, can you help me with multiple counts?
0

If you need to do that query, the most important part would be ensuring that all of your various fields have indexes. However, as each entry in each table has a one-to-one relationship with entries in the other tables, you're better off just using a single table.

Improve this answer

3 Comments

Its not one-to-one relationship. As you can see, that property_id 1 can have multiple location_type, multiple amentiy_type & multiple property_type.
Ah, in that case, have you considered using sets to store each class of attributes? dev.mysql.com/doc/refman/5.0/en/set.html
@Zack Bloom: Using sets or enums are not standart SQL, and MySQL implements them poorly which may lead to mistakes. My rule of thumb is never use enum or set unless you actually know what you're doing (which is really rare).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.