[Vtigercrm-developers] calendar optimization

[Adam Heinz]

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.

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.

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.

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.

[4]
http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L93

[5] mysql> desc vtiger_activity;
+------------------+--------------+------+-----+------------+-------+
| Field            | Type         | Null | Key | Default    | Extra |
+------------------+--------------+------+-----+------------+-------+
| activityid       | int(19)      | NO   | PRI | 0          |       |
| subject          | varchar(100) | NO   |     |            |       |
| semodule         | varchar(20)  | YES  |     | NULL       |       |
| activitytype     | varchar(200) | YES  | MUL | NULL       |       |
| date_start       | date         | NO   | MUL | 0000-00-00 |       |
| due_date         | date         | YES  | MUL | NULL       |       |
| time_start       | varchar(50)  | YES  |     | NULL       |       |
| time_end         | varchar(50)  | YES  |     | NULL       |       |
| sendnotification | char(3)      | NO   |     | 0          |       |
| duration_hours   | varchar(200) | YES  |     | NULL       |       |
| duration_minutes | varchar(200) | YES  |     | NULL       |       |
| status           | varchar(200) | YES  | MUL | NULL       |       |
| eventstatus      | varchar(200) | YES  | MUL | NULL       |       |
| priority         | varchar(200) | YES  |     | NULL       |       |
| location         | varchar(150) | YES  |     | NULL       |       |
| notime           | char(3)      | NO   |     | 0          |       |
| visibility       | varchar(50)  | NO   |     | all        |       |
| recurringtype    | varchar(200) | YES  |     | NULL       |       |
+------------------+--------------+------+-----+------------+-------+

[6] mysql> desc vtiger_recurringevents;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| recurringid   | int(19)     | NO   | PRI | NULL    | auto_increment |
| activityid    | int(19)     | NO   | MUL | 0       |                |
| recurringdate | date        | YES  | MUL | NULL    |                |
| recurringtype | varchar(30) | YES  |     | NULL    |                |
| recurringfreq | int(19)     | YES  |     | NULL    |                |
| recurringinfo | varchar(50) | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+




On Wed, Aug 14, 2013 at 10:35 AM, Prasad <prasad at vtiger.com> wrote:

> Adam,
>
> Interesting - can you share the details of changes that is required for
> based on Vtiger 6 Beta.
>
> Regards,
> Prasad
>
> *Connect with us on: *Twitter <http://twitter.com/vtigercrm> *I* Facebook<http://www.facebook.com/pages/vtiger/226866697333578?sk=wall>
>  *I* Blog <https://blogs.vtiger.com/>* I* Wiki<http://wiki.vtiger.com/index.php/Main_Page>
>  *I *Forums  <https://discussions.vtiger.com>*I* Website<https://www.vtiger.com/>
>
>
> On Wed, Aug 14, 2013 at 1:44 AM, Adam Heinz <amh at metricwise.net> wrote:
>
>> 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.  ;)
>>
>> [1]
>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/Appointment.php#L61
>> [2]
>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/6.0.0/modules/Calendar/Appointment.php#L61
>> [3]
>> http://trac.vtiger.com/cgi-bin/trac.cgi/browser/vtigercrm/branches/5.4.0/modules/Calendar/calendarLayout.php#L1357
>>
>>
>> _______________________________________________
>> http://www.vtiger.com/
>>
>
>
> _______________________________________________
> http://www.vtiger.com/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130814/da5eebfc/attachment.html>