Professional Web Applications Themes

Odd performance bottleneck - MySQL

Let's say I have three tables: realestate attribute attribute_list Attribute is a simple text column holding values like 'female' or 'smart' attribute list holds two foreign keys, pointing to attribute_id and realestate_id, so a normalized list of attributes can be attached in arbitrary combinations to the realestate table. Now I want to query for realestate where 'male', 'broker' and 'action_realty' attributes are attached. (action_realty is probably a poorly chosen example, but this is just a hypothetical example) SELECT w.* FROM realestate AS w JOIN attribute_list AS alist1 ON w.agent_id = alist1.agent_id JOIN attribute_list AS alist2 ON w.agent_id = alist2.agent_id JOIN ...

  1. #1

    Default Odd performance bottleneck

    Let's say I have three tables:
    realestate
    attribute
    attribute_list

    Attribute is a simple text column holding values like 'female' or
    'smart'
    attribute list holds two foreign keys, pointing
    to attribute_id and realestate_id, so a normalized
    list of attributes can be attached in arbitrary
    combinations to the realestate table.
    Now I want to query for realestate where
    'male', 'broker' and 'action_realty' attributes are attached.
    (action_realty is probably a poorly chosen example, but
    this is just a hypothetical example)

    SELECT w.*
    FROM realestate AS w
    JOIN attribute_list AS alist1 ON w.agent_id = alist1.agent_id
    JOIN attribute_list AS alist2 ON w.agent_id = alist2.agent_id
    JOIN attribute_list AS alist3 ON w.agent_id = alist3.agent_id

    JOIN attribute AS male ON alist1.attribute_id = male.attribute_id
    JOIN attribute AS broker ON alist2.attribute_id = broker.attribute_id
    JOIN attribute AS action_realty ON alist3.attribute_id =
    action_realty.attribute_id

    where male.attribute = 'male'
    and broker.attribute = 'broker'
    and action_realty.attribute='action_realty'

    .....then I get the right answer. But it is pathetically slow on a
    few as a thousand records.
    Ooops. I forgot to make indexes. So I reload the tables and the data,
    with appropriate indexes defined. Now it is much faster, but
    still dissapoiting. If I try to combine 6-10 attributes the time
    seems almost exponentially slow.

    However, if I do a similar thing (an ugly hack) with external
    php processing, the query runs much faster.
    That is, if I make one separate query to mysql for each attribute,
    and then update a hashed array $re_ids[$id]++
    for each hit on each separate query, and then collect
    all entries into the $re_id hash that have a count equal
    to the number of separate attribute queries, then I get the
    same result set, but in faster time than the aliased inner-join
    syntax, that did the same thing all in one sql statement.

    I would have guessed the opposite.
    Is there a way to make this query, all in one sql statement,
    in a more efficient manner, that at least equals the ugly
    php hack? (I have a mysql 4.something server, that won't do
    nested queries).

    Sandy.Pittendrigh@gmail.com Guest

  2. #2

    Default Re: Odd performance bottleneck

    [email]Sandy.Pittendrigh[/email] wrote:
    > Let's say I have three tables:
    > realestate
    > attribute
    > attribute_list
    >
    > Attribute is a simple text column holding values like 'female' or
    > 'smart'
    > attribute list holds two foreign keys, pointing
    > to attribute_id and realestate_id, so a normalized
    > list of attributes can be attached in arbitrary
    > combinations to the realestate table.
    > Now I want to query for realestate where
    > 'male', 'broker' and 'action_realty' attributes are attached.
    > (action_realty is probably a poorly chosen example, but
    > this is just a hypothetical example)
    >
    > [Statement]
    > Ooops. I forgot to make indexes. So I reload the tables and the data,
    > with appropriate indexes defined. Now it is much faster, but
    > still dissapoiting. If I try to combine 6-10 attributes the time
    > seems almost exponentially slow.
    Did you try "EXPLAIN $STATEMENT"? In the answer you can most likly see
    if and where a full table-scan is done.

    HTH,
    --
    Philipp Tölke
    PGP 0x96A1FE7A
    Philipp Tölke Guest

Similar Threads

  1. FMS X FCS (Performance)
    By tiago.braga in forum Macromedia Flash Flashcom
    Replies: 3
    Last Post: November 23rd, 09:35 PM
  2. laptop bottleneck
    By toberttobert webforumsuser@macromedia.com in forum Macromedia Director Basics
    Replies: 2
    Last Post: February 9th, 08:40 AM
  3. 10baseT ethernet - bottleneck or not?
    By Stan The Man in forum Mac Networking
    Replies: 13
    Last Post: November 16th, 09:06 PM
  4. DB2 performance
    By ChristineLim in forum IBM DB2
    Replies: 1
    Last Post: July 31st, 02:36 PM
  5. Outbound HTTP Connection Bottleneck
    By Peter Burke in forum ASP.NET Web Services
    Replies: 1
    Last Post: July 10th, 06:34 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139