SQL Query Benchmark needed!

[madmat]

Hey folks,

could you do me a favour and run this query:

SELECT  contacts.id,  contacts.first_name, contacts.last_name as name, contacts.salutation  , accounts.name account_name, jtl0.account_id account_id, contacts.phone_work , contacts.assigned_user_id  FROM contacts  LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id 
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0 where (( accounts.name IN ( SELECT name FROM accounts accounts_sq_1   LEFT JOIN accounts_cstm accounts_cstm_sq_1 ON accounts_sq_1.id = accounts_cstm_sq_1.id_c   where (accounts_sq_1.industry in ('AnyCompanyName'))  )))  ORDER BY name ASC

on your sugar instance and post the query time here?

It´s absolutely puzzling me, it seems a standard query to me, but with ~5.000 records in accounts and contacts each it takes about 120s on all MySQL setups I can get hold of.
If I add a join on the users table it comes up to 240s.

If some of you could take the minute and try it on their sugar instance i´d be grateful, just to see if the error is in my MySQL setup or something else.

Thank you!

Martin

[madmat]

P.S.
The full query, which you are probably unable to process due to the custom fields:

SELECT  contacts.id  ,contacts_cstm.anrede_titel_c,contacts_cstm.erstellt_db_c,contacts_cstm.import_quelle_c,contacts_cstm.pers_funct_c,contacts_cstm.zustand_db_c, CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')) as name, contacts.first_name , contacts.last_name , contacts.salutation  , accounts.name account_name, jtl0.account_id account_id, contacts.phone_work , contacts.assigned_user_id  FROM contacts   LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c   LEFT JOIN  accounts_contacts jtl0 ON contacts.id=jtl0.contact_id  AND jtl0.deleted=0
 LEFT JOIN  accounts accounts ON accounts.id=jtl0.account_id AND accounts.deleted=0
 AND accounts.deleted=0 where (( accounts.name IN ( SELECT name FROM accounts accounts_sq_1   LEFT JOIN accounts_cstm accounts_cstm_sq_1 ON accounts_sq_1.id = accounts_cstm_sq_1.id_c   LEFT JOIN  users jt0 ON jt0.id= accounts_sq_1.assigned_user_id AND jt0.deleted=0  AND jt0.deleted=0 where (accounts_sq_1.industry in ('Apparel')) AND accounts_sq_1.deleted=0 ))) AND contacts.deleted=0 ORDER BY name ASC LIMIT 0,21

[davidboris]

Hi,

Try putting some indexing on the fields you are using in 'where' condition.

[agcopley]

Run the query directly in mysql using the EXPLAIN statement..you may be surprised.
Rgds
Andrew