Results 1 to 4 of 4

Thread: Optimize query

  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Optimize query

    What is faster in MySQL:
    I want to execute a query and find all profiles which have 1 or interests from interests table

    Important notes:
    - all interests are originally checkboxes in internet explorer and are saves in the database.
    - table profile has many attributes and has no foreign key to table interests
    - table interests will almost never change (maybe a few times a year)
    - there will be about 3000 profiles

    [interest]
    id (smallint, primary key, indexed)
    desc (varchar 127, not indexed, not null)


    A.
    - select all interest id's from table interests which match 1 or more criteria
    --> select id as interests_ids from interests where id in (1,2,3,9,7);
    - and then select * from table profiles where xx = yy and where profile.id in (interests_ids)

    possible disadvantage: execute 2 queries (the less you have to go to mysql, the best it is for performance)


    B.
    imagine profiles has a column called interest where values in field interests are stored as "~1~2~3~4~5~25". This way, multiple values can be stored in just 1 field

    - select * from profiles where xx and where
    profiles.interest like '%~1'
    or profiles.interest like '%~2'
    or profiles.interest like '%~3'
    or profiles.interest like '%~9'
    or profiles.interest like '%~7'

    only 1 query, but like can be very slow and the query is longer in terms of characters (more bytes are to be send to the database).
    Also, the interest field is a varchar, which is slower than a smallint.


    C.
    select * from profiles as p, interests as i where p.xx = xxx and where i.id in (1,2,3,9,7)

    only one query, but will create a link/join table -> so maybe not performant when many persons making requests?

    if I first check i.id in (1,2,3,9,7) and then where p.xx = xxx, will it maybe be faster? I think not because a join table will always be generated. If I first p.xx = xxx, then I will narrow the profile table and the join will be smaller.

    What is the best solution in terms of performance, maintaince?
    I have been thinking about this question several days before asking in this forum. I hope someone can help me.

    Thanks
    JohnQM

  2. #2
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: Optimize query

    Originally posted by JohnQM
    What is faster in MySQL:
    I want to execute a query and find all profiles which have 1 or interests from interests table

    Important notes:
    - all interests are originally checkboxes in internet explorer and are saves in the database.
    - table profile has many attributes and has no foreign key to table interests
    - table interests will almost never change (maybe a few times a year)
    - there will be about 3000 profiles

    [interest]
    id (smallint, primary key, indexed)
    desc (varchar 127, not indexed, not null)

    - select * from profiles where xx and where
    profiles.interest like '%~1'
    or profiles.interest like '%~2'
    or profiles.interest like '%~3'
    or profiles.interest like '%~9'
    or profiles.interest like '%~7'

    only 1 query, but like can be very slow and the query is longer in terms of characters (more bytes are to be send to the database).
    Also, the interest field is a varchar, which is slower than a smallint.


    What is the best solution in terms of performance, maintaince?
    I have been thinking about this question several days before asking in this forum. I hope someone can help me.

    Interests should not be stored the way they are now... They should be in their own table

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    So, if I understand well, this should be a adequate table definition?

    [interest]
    id (smallint, primary key, indexed)
    desc (varchar 127, not indexed, not null)

    [interest_profile]
    interestId (smallint, primary key, indexed)
    profileId (smallint not indexed)

    [profile]
    id (smallint, primary key, indexed)
    some attributes

    Which query would probably be the fastest, A or C? (B should not be used anymore, right?)

    Thanks
    JohnQM

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by JohnQM
    So, if I understand well, this should be a adequate table definition?

    [interest]
    id (smallint, primary key, indexed)
    desc (varchar 127, not indexed, not null)

    [interest_profile]
    interestId (smallint, primary key, indexed)
    profileId (smallint not indexed)

    [profile]
    id (smallint, primary key, indexed)
    some attributes

    Which query would probably be the fastest, A or C? (B should not be used anymore, right?)

    Thanks
    JohnQM
    What is faster in MySQL:
    I want to execute a query and find all profiles which have 1 or interests from interests table

    Important notes:
    - all interests are originally checkboxes in internet explorer and are saves in the database.
    - table profile has many attributes and has no foreign key to table interests
    - table interests will almost never change (maybe a few times a year)
    - there will be about 3000 profiles

    [interest]
    id (smallint, primary key, indexed)
    desc (varchar 127, not indexed, not null)

    SELECT profiles.*
    FROM profiles p, interests i, interest_profile ip
    WHERE ip.profileId=p.id AND
    p.interestId=ip.interestId AND
    i.id IN (1, 2, 3, 7, 9);

    Add indexes to:
    interest: id
    interest_profile: (profileId, interedtId)
    profile: id

Posting Permissions

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