Hi list.
This is the problem formulation:
a) I have some query to select a number of ids, like this:
select id_1_tbl1.id as id_1
from tbl1 id_1_tbl1, tbl2 id_1_tbl2,...
where <id_1 condition>
Let's say this query runs 10 sec and returns 1000 records in the result set.
b) Now I have another query for different set if ids:
select id_2_tbl1.id as id_2
from tbl1 id_2_tbl1, tbl2 id_2_tbl2,...
where <id_2 condition>
This query runs 100 millisec and returns 25 ids.
c) And now I want to get multiplication of id_1 and id_2 (expecting in the
result set 1000*25 id
pairs). I can do streightforward as follows:
select id_1_tbl1.id as id_1, id_2_tbl1 as id_2
from tbl1 id_1_tbl1, tbl2 id_1_tbl2,...
tbl1 id_2_tbl1, tbl2 id_2_tbl2,...
where <id_1 condition>
and <id_2 condition>
It is important to note here that there is no connection in this query between
id_1 and id_2 in
the conditions - they are completely independent. This query returns expected
result set but runs
240 sec. From time analysis I can assume that condition for id_1 is evaluated
25 times (result set
for id_2 condition). This is very slow. Interesting that if I do have some
connecting condition
between id_1 condition and id_2 condition the resulting query will be executed
in same 10 sec,
which is fast.
For now the only workaround I've found so far is following:
1) Create temporary table with id_1 result set:
create table TEMP.tmp01
as
select id_1_tbl1.id as id_1
from tbl1 id_1_tbl1, tbl2 id_1_tbl2,...
where <id_1 condition>
2) Create another temporary table for id_2:
create table TEMP.tmp02
as
select id_2_tbl1.id as id_2
from tbl1 id_2_tbl1, tbl2 id_2_tbl2,...
where <id_2 condition>
3) Run multiplication query from temporary tables:
select id_1, id_2
from TEMP.tmp01, TEMP.tmp02
4) Drop temporary tables:
drop table TEMP.tmp01
drop table TEMP.tmp02
This whole scenario runs about 10-11 sec which is good, but the solution itself
looks pretty ugly.
The ideal solution would be if MaxDB could execute the initial multiplication
query with
reasonable performance, but I didn't succeed on this path. Does anyone have any
ideas how it can
be done in a less-nasty manner?
Thanks in advance.
Alexei Novakov.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
