Adam,<br><br><div>readAppointment function scope is limited to Vtiger 5, might not be used in Vtiger 6.</div><div><br>Regards,<br>Prasad<div><br clear="all"><div><span style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px"><b>Connect with us on: </b><a href="http://twitter.com/vtigercrm" style="color:rgb(0,0,204)" target="_blank">Twitter</a> <b>I</b> <a href="http://www.facebook.com/pages/vtiger/226866697333578?sk=wall" style="color:rgb(0,0,204)" target="_blank">Facebook</a> <b>I</b> <a href="https://blogs.vtiger.com/" style="color:rgb(0,0,204)" target="_blank">Blog</a><b> I</b> <a href="http://wiki.vtiger.com/index.php/Main_Page" style="color:rgb(0,0,204)" target="_blank">Wiki</a> <b>I </b><a href="https://discussions.vtiger.com" style="color:rgb(0,0,204)" target="_blank">Forums </a><b>I</b> <a href="https://www.vtiger.com/" style="color:rgb(0,0,204)" target="_blank">Website</a></span></div>
<br><br><div class="gmail_quote">On Wed, Aug 14, 2013 at 9:05 PM, Adam Heinz <span dir="ltr"><<a href="mailto:amh@metricwise.net" target="_blank">amh@metricwise.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><div><div>The fundamental issue that I'm trying to solve is that the LEFT JOIN [4] against vtiger_recurringevents requires the WHERE OR condition -- now you have to check both vtiger_activity.date_start [5] OR vtiger_recurringevents.recurringdate [6]. My best idea right now is to essentially make ALL events recurring with with recurringtype = Daily, repeating once. This gets you pretty close to being able to use an index on vtiger_recurringevents to do the bulk of the filtering. Unfortunately, this bumps up against a second issue.<br>
</div><div><br></div><div>It is currently possible to have a recurring event, say 2-4pm repeating every Monday. It is also possible to have a multi-day event, say 2pm Monday to 4pm Tuesday. It does not appear to be possible to have a recurring multi-day event. Though vtiger_recurringevents has a field to hold the start date of the recurrence, it does not have a second date field to hold the end date of the recurrence.</div>
<div><br></div><div>The end goal here is to have a single table that holds the start and end dates necessary to determine if a given event (or recurring instance of that event, or part of the multi-day event) should be displayed on a calendar.</div>
<div><br></div><div>I did briefly attempt using UNION to split the query into recurring and non-recurring halves, but there is a surprising amount of code that modifies the SQL after the fact, so I abandoned that effort temporarily. I haven't ruled it out yet.</div>
<div><br></div><div>[4] <a href="http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L93" target="_blank">http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L93</a></div>
<div><br></div><div>[5] mysql> desc vtiger_activity;</div><div>+------------------+--------------+------+-----+------------+-------+</div><div>| Field | Type | Null | Key | Default | Extra |</div>
<div>+------------------+--------------+------+-----+------------+-------+</div><div>| activityid | int(19) | NO | PRI | 0 | |</div><div>| subject | varchar(100) | NO | | | |</div>
<div>| semodule | varchar(20) | YES | | NULL | |</div><div>| activitytype | varchar(200) | YES | MUL | NULL | |</div><div>| date_start | date | NO | MUL | 0000-00-00 | |</div>
<div>| due_date | date | YES | MUL | NULL | |</div><div>| time_start | varchar(50) | YES | | NULL | |</div><div>| time_end | varchar(50) | YES | | NULL | |</div>
<div>| sendnotification | char(3) | NO | | 0 | |</div><div>| duration_hours | varchar(200) | YES | | NULL | |</div><div>| duration_minutes | varchar(200) | YES | | NULL | |</div>
<div>| status | varchar(200) | YES | MUL | NULL | |</div><div>| eventstatus | varchar(200) | YES | MUL | NULL | |</div><div>| priority | varchar(200) | YES | | NULL | |</div>
<div>| location | varchar(150) | YES | | NULL | |</div><div>| notime | char(3) | NO | | 0 | |</div><div>| visibility | varchar(50) | NO | | all | |</div>
<div>| recurringtype | varchar(200) | YES | | NULL | |</div><div>+------------------+--------------+------+-----+------------+-------+</div></div><div><br></div><div><div>[6] mysql> desc vtiger_recurringevents;</div>
<div>+---------------+-------------+------+-----+---------+----------------+</div><div>| Field | Type | Null | Key | Default | Extra |</div><div>+---------------+-------------+------+-----+---------+----------------+</div>
<div>| recurringid | int(19) | NO | PRI | NULL | auto_increment |</div><div>| activityid | int(19) | NO | MUL | 0 | |</div><div>| recurringdate | date | YES | MUL | NULL | |</div>
<div>| recurringtype | varchar(30) | YES | | NULL | |</div><div>| recurringfreq | int(19) | YES | | NULL | |</div><div>| recurringinfo | varchar(50) | YES | | NULL | |</div>
<div>+---------------+-------------+------+-----+---------+----------------+</div></div><div><br></div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div class="h5">On Wed, Aug 14, 2013 at 10:35 AM, Prasad <span dir="ltr"><<a href="mailto:prasad@vtiger.com" target="_blank">prasad@vtiger.com</a>></span> wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div class="h5">Adam,<div><br></div><div>Interesting - can you share the details of changes that is required for based on Vtiger 6 Beta.</div>
<div><br></div><div>Regards,</div><div>Prasad</div><div><br clear="all"><div><span style="border-collapse:collapse;font-family:arial,sans-serif;font-size:13px"><b>Connect with us on: </b><a href="http://twitter.com/vtigercrm" style="color:rgb(0,0,204)" target="_blank">Twitter</a> <b>I</b> <a href="http://www.facebook.com/pages/vtiger/226866697333578?sk=wall" style="color:rgb(0,0,204)" target="_blank">Facebook</a> <b>I</b> <a href="https://blogs.vtiger.com/" style="color:rgb(0,0,204)" target="_blank">Blog</a><b> I</b> <a href="http://wiki.vtiger.com/index.php/Main_Page" style="color:rgb(0,0,204)" target="_blank">Wiki</a> <b>I </b><a href="https://discussions.vtiger.com" style="color:rgb(0,0,204)" target="_blank">Forums </a><b>I</b> <a href="https://www.vtiger.com/" style="color:rgb(0,0,204)" target="_blank">Website</a></span></div>
<br><br><div class="gmail_quote"><div><div>On Wed, Aug 14, 2013 at 1:44 AM, Adam Heinz <span dir="ltr"><<a href="mailto:amh@metricwise.net" target="_blank">amh@metricwise.net</a>></span> wrote:<br></div>
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>
<div dir="ltr">We do a ton of scheduling here, so our calendars get hammered all the time. Some of our larger stores are starting to see performance problems due to various calendar queries running full index scans. The most prevalent examples are readAppointment [1][2] and getEventList [3]. Due to all the OR conjunctions in the WHERE clauses, MySQL is unable to determine a useful index, even after I've removed all of the CAST/CONCAT calls. Is anyone else having performance problems with their calendar? I have some fairly drastic ideas on how to solve the problem. ;)<div>
<br></div><div>[1] <a href="http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/Appointment.php#L61" target="_blank">http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/Appointment.php#L61</a><br>
</div><div>[2] <a href="http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L61" target="_blank">http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L61</a></div>
<div>[3] <a href="http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/calendarLayout.php#L1357" target="_blank">http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/calendarLayout.php#L1357</a><br>
</div><div><br></div></div>
<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></div>
<br>_______________________________________________<br>
<a href="http://www.vtiger.com/" target="_blank">http://www.vtiger.com/</a><br></blockquote></div><br></div></div>