<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
I think in a standard vtiger, you should have most of the indexes in
place, so try this:<br>
<br>
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<br>
<br>
I think the first line will have a big number in the 'rows' column.<br>
<br>
ALTER TABLE `vtiger_leaddetails` ADD INDEX ( `email` );<br>
ALTER TABLE `vtiger_leaddetails` ADD INDEX ( `firstname` );<br>
<br>
now try this again:<br>
<br>
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<br>
<br>
and finally, without the explain how long does this take?<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 = 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<br>
<br>
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 :)<br>
<br>
<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>