> EXPLAIN
> SELECT FA1, FA2, FA3, FAPayload1, FAPayload2, FAPayload3,
> FB1, FBPayload1
> FROM TestA, TestB
> WHERE FA3=10
> AND FA4=1
> AND TestA.FA2=TestB.FB1
>
> My Questions:
>
> 1. Explain output shows 154 PAGECOUNT for the Join. Are
> these 8KB MaxDB storage pages?
Yes.
> Does that mean 1232 kilobytes of Disk I/O?
Maybe. If nothing of the table data is in the cache, then it means also I/O.
> 2. Is there a better (performing) way to write such a join? Union?
> Subselect?
General recommendation: create an index on the join columns. The primary key
is fine here.
> 3. The SQL1.txt setup creates 6 indexes. For the EXPLAIN
> output, it shows that TESTAINDEX5 is being used. Would it not make
> more sense for the MaxDB engine to use TESTAINDEX6 - isn't that query
> an exact match with the WHERE clause?
No, you give no information on FB1.
The execution is as follows (see you explain output):
1. select all that is needed to satisfy the result set and the join
condition from TestA using FA3=10 AND FA4=1. (Which, BTW, is almost
everything from TestA.)
2. Then take the result set for FB1 and look what we have in TestB
that satisfies this condition.
It can't be the other way round.
> Isn't this causing more I/O than needed using a INDEX that has a
> unused field (FAPayload3 is on TESTAINDEX5 - but is not used
> in the WHERE).
Depending on the field contents the optimizer chooses the index that
costs less. This means: "less I/O".
> In our production application. TableA has about 4 million records and
> TableB has about 3.5 million records. The indexes and keys illustrated are
> the same type for our most frequent query. We know we can denormalize the
> two tables - but we also want to understand why there is so much disk I/O on
> this query - and why we do not understand the selection of INDEX.
Look at the explain output, have your statistics up to date, monitor your database.
HTH
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
