This is really strange. It is not identifying the rows in the subquery first and then using those in the main query. I know that you are unable to rewrite the query but perhaps we can run a test using a rewritten query to see how this responds?

SELECT col1, col2
FROM table b
JOIN (SELECT DISTINCT id FROM idxmail i JOIN matching m ON (i.proxyaddress = m.proxyaddress) WHERE m.mailnickname = '@var1') AS sub
ON (b.id = sub.id);

I would also have a look at the explain plan of this query.

One final question, what version of MySQL are you using?

iam sorry that i didnt mention that before.
The Version is 5.0.84

select Betreff, absender
from belege b
JOIN (SELECT DISTINCT id FROM idxmail i JOIN matchingtabelle m ON (i.proxyadresse = m.proxyadresse) WHERE m.mailnickname = 'brockmann@detectomat.com') AS sub
ON (b.dwdocid = sub.id);

-> 3565 rows in .08 seconds


"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "<derived2>" "ALL" NULL NULL NULL NULL "3565" ""
"1" "PRIMARY" "b" "eq_ref" "PRIMARY,DWDOCID" "PRIMARY" "4" "sub.id" "1" ""
"2" "DERIVED" "m" "ref" "Mailnickname,Proxyadresse" "Mailnickname" "768" "" "5" "Using where; Using temporary"
"2" "DERIVED" "i" "ref" "Proxyadresse" "Proxyadresse" "78" "dwdata.m.Proxyadresse" "18" "Using where"

We could try to get the subquery to process first by rewriting the query this way:

SELECT col1, col2
FROM table b
WHERE b.id IN (
SELECT DISTINCT id
FROM idxmail i
JOIN matching m ON i.proxyaddress = m.proxyaddress
WHERE m.mailnickname = '@var1')

This might identify the correct rows first in the subquery and then use this information in the main table.

Still the same result:

select Betreff, absender
from belegarchiv b
where b.dwdocid in (
SELECT DISTINCT id
FROM idxmail i
JOIN matchingtabelle m ON i.proxyadresse = m.proxyadresse
WHERE m.mailnickname = '@var1')


"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "b" "ALL" NULL NULL NULL NULL "837301" "Using where"
"2" "DEPENDENT SUBQUERY" "i" "ref" "ID,Proxyadresse" "ID" "5" "func" "1" "Using where; Using temporary"
"2" "DEPENDENT SUBQUERY" "m" "ref" "Mailnickname,Proxyadresse" "Proxyadresse" "78" "dwdata.i.Proxyadresse" "1" "Using where; Distinct"

I fear that without being able to rewrite this query it will be almost impossible to get this to work correctly. It is using a dependent subquery which means it is using the primary table using each dwdocid and then checking in the subquery to see if this exists.

You could try playing around with the subquery and see if it can be rewritten in such as way that this is processed first. Alternatively can you already populate a temporary table with the dwdocid and then select the temporary table in the subquery.

hm,
selecting a view in the subquery is even worse. Takes about 3 minutes.
I cant prepare one view for each possible @var1 anyway that would be more that 200 views.

"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "b" "ALL" NULL NULL NULL NULL "777104" "Using where"
"2" "DEPENDENT SUBQUERY" "<derived3>" "ALL" NULL NULL NULL NULL "3565" "Using where; Using temporary"
"3" "DERIVED" "m" "ref" "Mailnickname,Proxyadresse" "Mailnickname" "768" "" "5" "Using where; Using temporary"
"3" "DERIVED" "i" "ref" "Proxyadresse" "Proxyadresse" "78" "dwdata.m.Proxyadresse" "42" "Using where"

The problem in this case is it is using the primary table b and full scanning this. Have you considered upgrading the server to a later version 5.5?

I tried the same with 5.5.28 now.
Same results, about the same duration.

It's a pity you cannot change the SQL statement. As you can see we can get 0.08seconds for execution time by changing this. Let me think about this more and see if there is anything we can do with this. Unfortunately it appears that the primary table is doing a full tablescan rather than using the ID's returned by the subquery and using the index to pick those entries.

Found out, that the result list limit seems not to be hardcoded.
In facts its just hidden in some longtext xml column.

Limiting the result list does reduce the cost significantly.
I dont get the 0.08 sec but is a good bit below a second.

Thank you alot for your help!

That is good news!! It is a pain when you cannot touch the original code to make sure that it is written more efficiently for your own use but if this works that is better than nothing. Good luck!!