Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Attribute search

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by krontrex View Post
    ...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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What's inefficient?
    Code:
    SELECT profession
    FROM people
    WHERE sex = 'M'
    AND age > 35
    AND city = 'NY'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by pootle flump View Post
    What's inefficient?
    Code:
    SELECT profession
    FROM people
    WHERE sex = 'M'
    AND age > 35
    AND city = 'NY'
    SELECT DISTINCT profession ...

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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...)

  6. #6
    Join Date
    Sep 2010
    Posts
    40

    redundancy

    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.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    post #3, with the correction in post #4.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by krontrex View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •