Slow Join

[adminaia]

Hello!
I'm working with apatar for a week and i think it is easy to learn. Brings fast solutions.
But today i faced a big problem while JOINING two MYSQL tables with about 370k records in. Each!

I build a first connector to table CONTACT and a second one to table ADRESS. Joining them all took hours (days?). I killed the job meanwhile.

Reducing the amount of values with WHERE lastname = 'smith' in table1 and where country = 'italy' in table2 will produce a fast result.

My second approach was to use the generic ODBC connector for mysql. It is faster to configure but i can not select different values with WHERE.

Any idea how to speed up?

[Sergei]

Hello,

You may try to write SQL query within MySQL connector to join two tables instead of using Join node. It would definitely speed up the process.
_________________
Regards,
Sergei

[adminaia]

Thanks for your answer,
it is pretty fast now!

[adminaia]

Ok, but actually I faced a problem joining 3 tables with inner join.

Two tables work fine:
Select * From `vtiger_contactdetails` Inner Join `vtiger_contactaddress` On contactid = contactaddressid

But this won't:
Select * From (`vtiger_contactdetails` Inner Join `vtiger_contactaddress` On contactid = contactaddressid) Inner Join `vtiger_contacscf` on vtiger_contactdetails.contactid = vtiger_contactcf.contactid

Or can I access a MySQL-View somehow?

[adminaia]

I speed up Join-Node #1 by filtering my 1st MySql-Node.
And my second Join-Node has only 20.000 values to match.

But it would be very nice if someone could check how to JOIN
3 tables in ONE SQL-Node.

Greetings