Weir slow Query

[jossanvi]

Load time for contacts or opportunities module is as high 90 seconds.

Tracking down the problem I figure out it wos due to count query e.g.

SELECT count(*) c FROM contacts 
    LEFT JOIN users ON contacts.assigned_user_id=users.id
    LEFT JOIN accounts_contacts ON contacts.id=accounts_contacts.contact_id  and accounts_contacts.deleted = 0
    LEFT JOIN accounts ON accounts_contacts.account_id=accounts.id   
    LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c where  contacts.deleted=0  
                                           AND (accounts.deleted is NULL or accounts.deleted=0)

If I set any filters in the windows load times is miliseconds.

Thaks for yhe help!!!

[andopes]

Hi, jossanvi.

How big are your contact and opportunity tables?
I believe you can solve the problem adding some indexes at its tables.
You can do that using extended vardefs and after repairing database.

Cheers

--
André Lopes
Lâmpada Global Services
Rua Bela Cintra, 299 conjunto. 51
São Paulo, SP 01415-000
tel1. 55 11 3237-3110
cel. 55 11 7636-5859
e-mail: info@lampadacrm.com.br

[jossanvi]

Hi andrés!..

thanks for the response.

We have around 180 thousand contacts and a little bit more opportunities..

I am monitoring key performance and looks good.. its weird but only the select count(*) is the one that takes long time, the rest is ok.

Thanks for any light you may provide on this issue!


cheers

jose

[SugarDev.net]

It's just a very big query. It's because of the nature of SugarCRM. You can do this in config_override.php:

$sugar_config['disable_count_query'] = TRUE;

..to disable it. Or, you can maybe do something with a few indices.

[jossanvi]

Thanks for the replay SugarDev.net,



I input de php code that you give me and it helps me so much with my problem


Now, I see in the log of mysql that, when I coming into one opportunity or contact, the crm make other select count (*)... in my case the select that it make in the opportunity is this:

SELECT count(*) c FROM opportunities LEFT JOIN users

                            ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities

                            ON opportunities.id=accounts_opportunities.opportunity_id

                            LEFT JOIN accounts

                            ON accounts_opportunities.account_id=accounts.id  LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c where

                        (accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0)

                        AND (accounts.deleted is null OR accounts.deleted=0)

                        AND opportunities.deleted=0

And load the information high of 30 seconds that is so slow.


Thanks for all your help!!