Thanks for the reply.
I can try to explain the query but it is being generated semi-automatically
so it is not hard coded for a particular purpose. Before going into the
explanation, though, I have a litte bit of new information. First, it is
ONLY the join condition that matters, the other parameters do not make a
difference in terms of memory usage. That is, the following, simplified
query, uses the same amount of memory. Also having or removing the DISTINCT
makes no difference. Also, for some VERY odd reason, adding a 10th
constraint caused the EXPLAIN to take significantly LESS memory but the
actual query still took much MORE memory.
> SELECT DISTINCT product.product_id
> FROM product,
> pr_prop_str alias_table_0,
> pr_prop_str alias_table_1,
> pr_prop_str alias_table_2,
> pr_prop_str alias_table_3,
> pr_prop_str alias_table_4,
> pr_prop_str alias_table_5,
> pr_prop_str alias_table_6,
> pr_prop_str alias_table_7,
> pr_prop_str alias_table_8
> WHERE product.product_id = alias_table_0.product_id
> AND product.product_id = alias_table_1.product_id
> AND product.product_id = alias_table_2.product_id
> AND product.product_id = alias_table_3.product_id
> AND product.product_id = alias_table_4.product_id
> AND product.product_id = alias_table_5.product_id
> AND product.product_id = alias_table_6.product_id
> AND product.product_id = alias_table_7.product_id
> AND product.product_id = alias_table_8.product_id;
Obviously this query isn't particularly interesting by itself but it does,
perhaps, simplify the problem. If you create a table called 'product' with
'product_id' and a table called 'pr_prop_str' with 'product_id', then you
can test the above query. For me, even with minimal data in these tables the
query still took ~60MB. As for an explanation of the full query:
What is happening is that a 'product' is being searched for that fulfills a
number of criteria that are specified in the pr_prop_str (product properties
strings) table. So we join all the product IDs to make sure the product has
all of the required properties. Then we add in the particular property
conditions. Each property has an ID (for example, the first pr_property_id
is 147, that might coorespond to a model number or something like that) that
we use in conjunction with the particular requirement (in this example, the
model number must be '3E362cb').
I hope that makes sense.
--Rainer