[Vtigercrm-developers] performance turning tips : listview search

[lajeesh k]

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 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> 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/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20150925/d88df85e/attachment.html>