Attribute search

krontrex · #1 (**permalink**) 10-08-10, 08:18

Hi,
I have a following problem, I have an object that represents a person, member of a team. The attributes are name, last name, age, sex, education, city of birth, profession, etc.). Now, I would like to make a query that relates only to attributes without requiring actually information about person. For example, I would like to find out of what professions are male members, older than 35 years and born in New York. I can make query that indirectly searches through relative attributes of all persons but it is inefficient because there is lot of redundancy involved. Is there any way to optimize this kind of search?
Thank you

r937 · #2 (**permalink**) 10-08-10, 08:30

Quote:

Originally Posted by krontrex

...but it is inefficient because there is lot of redundancy involved.

redundancy? what redundancy?

optimization is accomplished with indexes on columns, and will work only if the query requires some meaningful restriction of values in that column

for instance, your query "what professions are male members, older than 35 years and born in New York" might use an index on city or on age, but likely not sex (because of low cardinality)

pootle flump · #3 (**permalink**) 10-08-10, 08:31

What's inefficient?

Code:

SELECT profession
FROM people
WHERE sex = 'M'
AND age > 35
AND city = 'NY'

andrewst · #4 (**permalink**) 10-08-10, 08:44

Quote:

Originally Posted by pootle flump

What's inefficient?

Code:

SELECT profession
FROM people
WHERE sex = 'M'
AND age > 35
AND city = 'NY'

SELECT DISTINCT profession ...

andrewst · #5 (**permalink**) 10-08-10, 08:46

I do hope we're not dealing with an EAV model here:

Code:

insert into person_attributes (person_id, att_name, att_value)
values (123, 'Name', 'Smith');
insert into person_attributes (person_id, att_name, att_value)
values (123, 'Sex', 'Male');
insert into person_attributes (person_id, att_name, att_value)
values (123, 'DOB', '01-JAN-1970');

(It is the talk of objects and attributes that makes me fear we may be...)

krontrex · #6 (**permalink**) 10-08-10, 09:14

Thanks for the answers.
It is not an EAV model.
I meant by redundancy there may be many people of same profession that satisfy the query, is there any way to group them on the basis of their profession and after finding the first member to exempt rest of them from the search.

pootle flump · #7 (**permalink**) 10-08-10, 09:20

post #3, with the correction in post #4.

andrewst · #8 (**permalink**) 10-09-10, 04:34

Quote:

Originally Posted by krontrex

Thanks for the answers.
It is not an EAV model.
I meant by redundancy there may be many people of same profession that satisfy the query, is there any way to group them on the basis of their profession and after finding the first member to exempt rest of them from the search.

I'm glad it isn't EAV

I think you are trying to optimise on behalf of the database here: that's not the way a relational database works, or it least it shouldn't be. You just "declaratively" specify the result you want, which as Pootle says is:

Code:

SELECT DISTINCT profession
FROM people
WHERE sex = 'M'
AND age > 35
AND city = 'NY'

and then the database figures out the most efficient way to answer that question.

Internet Services

Web Development

Digital Marketing

Consumer Tech