idem with this column : <span class="gen">vtiger_troubletickets.</span><span class="gen">product_id and </span><span class="gen">vtiger_troubletickets.</span><span class="gen">parent_id</span><br><br><span class="gen"><span style="color: rgb(255, 164, 149);"><b>ALTER</b></span> <span style="color: rgb(255, 164, 149);"><b>TABLE</b></span> `vtiger_troubletickets` CHANGE `product_id` `product_id` INT( 19 ) NULL DEFAULT NULL;
<br>
<br>
<span style="color: rgb(255, 164, 149);"><b>ALTER</b></span> <span style="color: rgb(255, 164, 149);"><b>TABLE</b></span> `vtiger_troubletickets` CHANGE `parent_id` `parent_id` INT( 19 ) NULL DEFAULT NULL;
</span><br><br><div class="gmail_quote">2010/12/8 Aissa belaid <span dir="ltr"><<a href="mailto:aissabelaid@gmail.com">aissabelaid@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
I have no access for vtiger trac<br><br><div class="gmail_quote"><div><div></div><div class="h5">2010/12/8 David V. <span dir="ltr"><<a href="http://davidv.net" target="_blank">davidv.net</a>@<a href="http://gmail.com" target="_blank">gmail.com</a>></span><br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">
Thank you for pointing this out.<div><br></div><div>Maybe you could also report it in Trac.</div><div><br clear="all">David V.<br>
<br><br><div class="gmail_quote"><div><div></div><div>2010/12/8 Aissa belaid <span dir="ltr"><<a href="mailto:aissabelaid@gmail.com" target="_blank">aissabelaid@gmail.com</a>></span><br></div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div></div><div>
Hi David,<br>because you have a join between integer col & varchar col in the sql query.<br>With 100 line, no problem ;-) <br>With 100 000 you can wait 2h.<br><br>Exemple query for contacts :<br><br><br>select vtiger_contactdetails.contactid as recordid,vtiger_users_last_import.deleted,$table_cols <br>
FROM vtiger_contactdetails<br> INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_contactdetails.contactid<br> INNER JOIN vtiger_contactaddress ON vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid<br>
INNER JOIN vtiger_contactsubdetails ON vtiger_contactaddress.contactaddressid = vtiger_contactsubdetails.contactsubscriptionid<br> LEFT JOIN vtiger_contactscf ON vtiger_contactscf.contactid = vtiger_contactdetails.contactid <br>
<b> LEFT JOIN vtiger_users_last_import ON vtiger_users_last_import.bean_id=vtiger_contactdetails.contactid*</b><br> LEFT JOIN vtiger_account ON vtiger_account.accountid=vtiger_contactdetails.accountid<br>
LEFT JOIN vtiger_customerdetails ON vtiger_customerdetails.customerid=vtiger_contactdetails.contactid<br> LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid<br>
LEFT JOIN vtiger_users ON <a href="http://vtiger_users.id" target="_blank">vtiger_users.id</a> = vtiger_crmentity.smownerid<br> INNER JOIN (select $select_clause from vtiger_contactdetails t<br>
INNER JOIN vtiger_crmentity crm ON crm.crmid=t.contactid<br>
INNER JOIN vtiger_contactaddress addr ON t.contactid = addr.contactaddressid<br> INNER JOIN vtiger_contactsubdetails subd ON addr.contactaddressid = subd.contactsubscriptionid<br>
LEFT JOIN vtiger_contactscf tcf ON t.contactid = tcf.contactid <br> LEFT JOIN vtiger_account acc ON acc.accountid=t.accountid<br> LEFT JOIN vtiger_customerdetails custd ON custd.customerid=t.contactid<br>
WHERE crm.deleted=0 group by $select_clause HAVING COUNT(*)>1) as temp<br> ON ".get_on_clause($field_values,$ui_type_arr,$module)."<br> WHERE vtiger_crmentity.deleted=0 $sec_parameter ORDER BY $table_cols,vtiger_contactdetails.contactid ASC<br>
<br>*Here uou kill the perfs. Contactid is an INT type, and bean_id a varchar.<br>You don't need this patch for small database.<br><br><br><div class="gmail_quote">2010/12/8 David V. <span dir="ltr"><<a href="http://davidv.net" target="_blank">davidv.net</a>@<a href="http://gmail.com" target="_blank">gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div>Aïssa,<div><br></div><div>Could you explain why it improves perfs ?</div><div><br clear="all">
David V.<br>
<br><br><div class="gmail_quote">2010/12/8 Aissa belaid <span dir="ltr"><<a href="mailto:aissabelaid@gmail.com" target="_blank">aissabelaid@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div></div><div>
Hi team,<br>there is a problem with database schema for duplicate handling & import function.<br>This is a fix to improve 10x the perfs:<br>ALTER TABLE `vtiger_users_last_import` CHANGE `assigned_user_id` `assigned_user_id` INT( 19 ) NULL DEFAULT NULL ,<br>
CHANGE `bean_id` `bean_id` INT( 19 ) NULL DEFAULT NULL<br clear="all">Cheer,<br><font color="#888888">-- <br>Aïssa Bélaïd,<br>Expert CRM/BI<br>Email : <a href="mailto:aissabelaid@gmail.com" target="_blank">aissabelaid@gmail.com</a><br>
Blog : <a href="http://www.aissabelaid.fr" target="_blank">www.aissabelaid.fr</a><br>
<br>
</font><br></div></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>
<br></div></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br>
<br></div></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div>
<br></div></div>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><div><div></div><div class="h5"><br><br clear="all"><br>-- <br>Aïssa Bélaïd,<br>Expert CRM/BI<br>Email : <a href="mailto:aissabelaid@gmail.com" target="_blank">aissabelaid@gmail.com</a><br>
Blog : <a href="http://www.aissabelaid.fr" target="_blank">www.aissabelaid.fr</a><br><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>Aïssa Bélaïd,<br>Expert CRM/BI<br>Email : <a href="mailto:aissabelaid@gmail.com">aissabelaid@gmail.com</a><br>Blog : <a href="http://www.aissabelaid.fr">www.aissabelaid.fr</a><br>
<br>