<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
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. <br>
You can also look at the things you are joining on, <br>
vtiger_leaddetails.leadid <br>
vtiger_leadsubdetails.leadsubscriptionid<br>
vtiger_crmentity.smownerid <br>
vtiger_crmentity.crmid<br>
vtiger_users.id<br>
vtiger_groups.groupid<br>
<br>
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.<br>
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. <br>
<br>
Alan.<br>
<br>
<div class="moz-cite-prefix">On 25/09/15 16:28, lajeesh k wrote:<br>
</div>
<blockquote
cite="mid:CAA1bLmrGP4BR=JZQaEMZrpfyRokLsuaYq6VfJmghR0Ezpz8HRg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>Thank you Alan for your tips,<br>
<br>
</div>
this count query takes 30 seconds <br>
<br>
<br>
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 = <a moz-do-not-send="true"
href="http://vtiger_users.id">vtiger_users.id</a> 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;<br>
<br>
<div><br>
</div>
<div>i am not good at mysql query optimisation. anyway I will
learn for this task<br>
</div>
<div>If you find any clue in quick look please let me know<br>
<br>
<br>
</div>
<div><br>
</div>
<div class="gmail_extra"><br clear="all">
<div>
<div class="gmail_signature"><br>
Regards,<br>
Lajeesh<br>
</div>
</div>
<br>
<div class="gmail_quote">On Fri, Sep 25, 2015 at 7:53 PM, Alan
Bell <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:alan.bell@libertus.co.uk" target="_blank">alan.bell@libertus.co.uk</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF"> 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.<br>
<br>
Alan.
<div>
<div class="h5"><br>
<br>
<div>On 25/09/15 15:17, lajeesh k wrote:<br>
</div>
</div>
</div>
<blockquote type="cite">
<div>
<div class="h5">
<div dir="ltr">
<div>
<div>
<div>Hi <br>
<br>
</div>
<div>I have more than a million records<br>
</div>
can I get performance turning tips for
faster listview search<br>
</div>
need this mainly in leads<br>
</div>
tuning like settings foreign keys<br>
<br>
<br clear="all">
<div>
<div>
<div>
<div>
<div>
<div><br>
Regards,<br>
Lajeesh<br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<br>
</div>
</div>
<pre>_______________________________________________
<a moz-do-not-send="true" href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a></pre>
</blockquote>
<br>
</div>
<br>
_______________________________________________<br>
<a moz-do-not-send="true" href="http://www.vtiger.com/"
rel="noreferrer" target="_blank">http://www.vtiger.com/</a><br>
</blockquote>
</div>
<br>
</div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
<a class="moz-txt-link-freetext" href="http://www.vtiger.com/">http://www.vtiger.com/</a></pre>
</blockquote>
<br>
</body>
</html>