<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /></head><body style='font-size: 10pt; font-family: Verdana,Geneva,sans-serif'>
<p>The entire Calendar should be rewritten from the scratch. One query isn’t a problem because there are various mechanisms for various triggers encoded in it [it was optimized separately for loading of the first view, separately for changing of users after AJAX and separately for changing of a month/week/year]. Once we spent 3 days in this Calendar and the optimizations that we performed were as follows: a change of functions responsible for downloading data, a change of queries, an optimization of keys and tables [we probably copied a delete field to an activity table in handler so the main table didn’t have to be combined]. However, all we did were just half measures.</p>
<p>The Calendar also proves that no one ever deployed Vtiger in a large company that uses calendars. Vtiger has also a bad practice of overwriting root libraries or changing data on the fly [that’s why when a huge calendar is generated, operations on html are performed again].</p>
<p>We have mentioned a problem with the Calendar many times, but no one took care of it. If you need, I can generate a base for Vtiger 6.2 with any number of records so you can see the scale of the problem. We rewrote the Calendar in YF and it uses the most up-to-date fullcalendar library, has integration with CalDAV and half of the code and it works a lot faster. Unfortunately, our Calendar cannot be copied to Vtiger 6.2 in a relatively simple way because we optimize the engine for functionalities. I think it might be a good idea to look at our solutions because some of them may seem worth adding to Vtiger 6.3.</p>
<div>---<br />
<div>Z poważaniem / Regards</div>
<div> </div>
<div><strong>Błażej Pabiszczak</strong></div>
<div><em>Chief Executive Officer</em></div>
<div>M: +48.884999123<br />E: <a title="Mail do Błażej Pabiszczak" href="mailto:b.pabiszczak@yetiforce.com">b.pabiszczak@yetiforce.com</a></div>
<hr />
<p><span>We created an innovative open source project called YetiForceCRM. You can test it </span><a href="https://test.yetiforce.com" rel="noreferrer">here</a><span>, </span><a href="https://github.com/YetiForceCompany/YetiForceCRM" rel="noreferrer">download</a><span> it for free or read its </span><a href="https://yetiforce.com/en/documentation.html" rel="noreferrer">documentation</a><span>. Follow us on </span><a href="https://twitter.com/YetiForceEN" rel="noreferrer">Twitter</a><span> to get real-time info about new functionalities and articles.</span></p>
</div>
<p> </p>
<p>W dniu 2015-05-18 13:10, Alan Lord napisał(a):</p>
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0"><!-- html ignored --><!-- head ignored --><!-- meta ignored -->
<div class="pre" style="margin: 0; padding: 0; font-family: monospace">On 18/05/15 10:35, Vikas Jain wrote:
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Alan,<br /><br /> It was added to force the mysql use the table index, can you try the<br /> same query by removing the indexes added on activitytype and<br /> modifiedtime and comment the code and see if the indexes are having any<br /> influence on the query.</blockquote>
<br /> Hi Vikas,<br /><br /> This was quite interesting...<br /><br /> The activitytype index seems to make negligible difference to the query time.<br /><br /> I am a bit confused by the modifiedtime index however...<br /><br /> When I ran the query this morning (with the "AND vtiger_activity.activityid > 0" line included) the query ran reasonably fast with an average of around 0.4 seconds.<br /><br /> I then dropped the modifiedtime index and tried running the same query again. The query timed-out and never finished.<br /><br /> I then re-created the modifiedtime index, which took about 12 seconds to build, and tried the query again. It still times out.<br /><br /> I did notice that *after* mysql had said the index was created the Cardinality figure was very low (like a few hundred) and after several minutes it appeared to have increased (it's now 2673279). But the query was still not completing.<br /><br /> If I remove the "AND vtiger_activity.activityid > 0" clause from the query it returns nice and fast. If I include it again, the query times out.<br /><br /> So I am a bit confused by this index on the modifiedtime column. It almost seems like MySQL might have spent the weekend building some kind of index in the background which would then explain why it was fine before I deleted the index, and why it isn't fine after re-building it this again morning. But this would not seem to be the way MySQL works from what I have read so I am confused.<br /><br /> Nevertheless, I still do not see why this line is needed at all:<br /><br /> "AND vtiger_activity.activityid > 0"<br /><br /> because this join makes it totally redundant IMHO<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">INNER JOIN vtiger_crmentity<br /> ON vtiger_activity.activityid = vtiger_crmentity.crmid</blockquote>
<br /> And, more to the point, if I remove it from the query it runs fast. With it, it is too slow.<br /><br /> Cheers<br /><br /> Al<br /><br /> _______________________________________________<br /><a href="http://www.vtiger.com/">http://www.vtiger.com/</a></div>
</blockquote>
</body></html>