[Vtigercrm-developers] performance turning tips : listview search

[Alan Bell]

I think in a standard vtiger, you should have most of the indexes in 
place, so try this:

explain SELECT count(*) AS count  FROM vtiger_leaddetails  INNER JOIN 
vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid 
INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = 
vtiger_leadaddress.leadaddressid INNER JOIN vtiger_leadsubdetails ON 
vtiger_leaddetails.leadid = vtiger_leadsubdetails.leadsubscriptionid 
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id 
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = 
vtiger_groups.groupid  WHERE vtiger_crmentity.deleted=0 and 
vtiger_leaddetails.converted=0 AND (( vtiger_leaddetails.firstname LIKE 
'terry%')  and ( vtiger_leaddetails.email LIKE 'el-tel%') ) AND 
vtiger_leaddetails.leadid > 0

I think the first line will have a big number in the 'rows' column.

ALTER TABLE  `vtiger_leaddetails` ADD INDEX (  `email` );
ALTER TABLE  `vtiger_leaddetails` ADD INDEX (  `firstname` );

now try this again:

explain SELECT count(*) AS count  FROM vtiger_leaddetails  INNER JOIN 
vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid 
INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = 
vtiger_leadaddress.leadaddressid INNER JOIN vtiger_leadsubdetails ON 
vtiger_leaddetails.leadid = vtiger_leadsubdetails.leadsubscriptionid 
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id 
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = 
vtiger_groups.groupid  WHERE vtiger_crmentity.deleted=0 and 
vtiger_leaddetails.converted=0 AND (( vtiger_leaddetails.firstname LIKE 
'terry%')  and ( vtiger_leaddetails.email LIKE 'el-tel%') ) AND 
vtiger_leaddetails.leadid > 0

and finally, without the explain how long does this take?

SELECT count(*) AS count  FROM vtiger_leaddetails  INNER JOIN 
vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid 
INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = 
vtiger_leadaddress.leadaddressid INNER JOIN vtiger_leadsubdetails ON 
vtiger_leaddetails.leadid = vtiger_leadsubdetails.leadsubscriptionid 
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id 
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = 
vtiger_groups.groupid  WHERE vtiger_crmentity.deleted=0 and 
vtiger_leaddetails.converted=0 AND (( vtiger_leaddetails.firstname LIKE 
'terry%')  and ( vtiger_leaddetails.email LIKE 'el-tel%') ) AND 
vtiger_leaddetails.leadid > 0

that is with the query modified to drop the leading % so that it can use 
the indexes, I am guessing it won't take much time at all :)



On 25/09/15 16:28, lajeesh k wrote:
> Thank you Alan for your tips,
>
> this count query takes 30 seconds
>
>
> SELECT count(*) AS count  FROM vtiger_leaddetails  INNER JOIN 
> vtiger_crmentity ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid 
> INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = 
> vtiger_leadaddress.leadaddressid INNER JOIN vtiger_leadsubdetails ON 
> vtiger_leaddetails.leadid = vtiger_leadsubdetails.leadsubscriptionid 
> LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id 
> <http://vtiger_users.id> LEFT JOIN vtiger_groups ON 
> vtiger_crmentity.smownerid = vtiger_groups.groupid  WHERE 
> vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0 AND   (( 
> vtiger_leaddetails.firstname LIKE '%terry%')  and ( 
> vtiger_leaddetails.email LIKE '%el-tel%') ) AND 
> vtiger_leaddetails.leadid > 0;
>
>
> i am not good at mysql query optimisation. anyway I will learn for 
> this task
> If you find any clue in quick look please let me know
>
>
>
>
>
> Regards,
> Lajeesh
>
> On Fri, Sep 25, 2015 at 7:53 PM, Alan Bell <alan.bell at libertus.co.uk 
> <mailto:alan.bell at libertus.co.uk>> wrote:
>
>     you need to turn on and look at your database slow query log and
>     log all queries not using indexes. If you are seeing unindexed
>     queries then you need to index the relevant columns, this involves
>     lots of putting the queries into the mysql console or PHPmyadmin
>     and doing an "explain select" and interpretting the results you
>     get back, then you add indexes or tweak the code to do more
>     efficient joins and use the indexes more often. You can also use
>     xdebug to profile the code that vtiger runs, in that you will find
>     that the to_html function is the most called function that takes
>     the most time, so you can improve that by adding a cache, and/or
>     other tests that speed that process up.
>
>     Alan.
>
>
>     On 25/09/15 15:17, lajeesh k wrote:
>>     Hi
>>
>>     I have  more than a million records
>>     can I get performance turning tips for faster listview search
>>     need this mainly in leads
>>     tuning like settings foreign keys
>>
>>
>>
>>     Regards,
>>     Lajeesh
>>
>>
>>     _______________________________________________
>>     http://www.vtiger.com/
>
>
>     _______________________________________________
>     http://www.vtiger.com/
>
>
>
>
> _______________________________________________
> http://www.vtiger.com/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20150925/5da98eb8/attachment-0001.html>