[Vtigercrm-developers] performance turning tips : listview search

[Alan Bell]

OK, so this is doing a column search: LIKE '%terry%' note the wildcard 
at the start and end of the string, the one at the start is a bit of a 
killer, it means it can't use an index to rapidly find the answer, it 
has to do a brute force search on all the records and do a substring 
search of the firstname. If you can compromise and only allow starts 
with searches then you can make this way faster, so hack the code so 
that the query is LIKE 'terry%' and add an index on the firstname and 
emails columns it will be pretty much instant - at the cost of not 
allowing you to search for values in the middle of the field.
You can also look at the things you are joining on,
vtiger_leaddetails.leadid
vtiger_leadsubdetails.leadsubscriptionid
vtiger_crmentity.smownerid
vtiger_crmentity.crmid
vtiger_users.id
vtiger_groups.groupid

and make sure they are all indexed, along with 
vtiger_leaddetails.converted which is in the where clause. You can see 
things more clearly if you do an explain of the query, you will see some 
bits require looking at loads of rows, others hardly any, if you add an 
index in the right place then the number of rows it needs to look at to 
perform your query will go down.
There is a slight cost to indexing things, in terms of disk space and 
insert performance so try to only index things that need it - although, 
if users are adding their own filters with column searches that could be 
pretty much everything.

Alan.

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/7119eb97/attachment.html>