[Vtigercrm-developers] calendar performance bottleneck
Asha
asha at vtiger.com
Fri Feb 10 08:16:33 PST 2012
Hi Adam,
That's a good start. Hope you find good progress with Calendar query
optimization. Please do update us on your research and the results.
On Fri, Feb 10, 2012 at 3:02 AM, Adam Heinz <amh at metricwise.net> wrote:
> Up until recently, we hadn't used recurring events. Now that we're
> starting to get a decent number of them in the system, I've started
> seeing some degrading performance. (We use the calendar a LOT!) I
> explained the SQL created by calendarLayout.php getEventList() and
> didn't like what I saw. (Exhibit A) Type ALL! It's doing a table
> scan on every page load! I have been fiddling around a bit and hit
> upon the idea of splitting the recurring and non-recurring parts of
> the query into opposing halves of a UNION and added an index on
> recurringdate. (Exhibit B) Check out how the query planner inverted
> the table order to get at that juicy recurringdate index. I have only
> profiled this, not tried it. :) I will report back after I coerce
> PHP to generate the SQL I hand wrote and load test the result. As
> convenient as mkCountQuery() and fixPostgresQuery() are, they do not
> play nicely with unions.
>
> EXHIBIT A
>
> EXPLAIN EXTENDED SELECT vtiger_groups.groupname,
> vtiger_users.user_name,vtiger_crmentity.smownerid,
> vtiger_crmentity.crmid, vtiger_activity.*
> FROM vtiger_activity
> INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
> vtiger_activity.activityid
> LEFT JOIN vtiger_groups ON vtiger_groups.groupid =
> vtiger_crmentity.smownerid
> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
> LEFT JOIN vtiger_recurringevents ON vtiger_recurringevents.activityid
> = vtiger_activity.activityid
> WHERE vtiger_crmentity.deleted = 0
> AND (vtiger_activity.activitytype not in ('Emails','Task'))
> AND (((vtiger_activity.date_start >= '2012-02-09' AND
> vtiger_activity.due_date <= '2012-02-09')
> AND (vtiger_recurringevents.activityid is NULL))
> OR (vtiger_recurringevents.recurringdate BETWEEN '2012-02-09' AND
> '2012-02-09'))\G
>
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: vtiger_activity
> type: ALL
> possible_keys:
> PRIMARY,activity_activityid_subject_idx,activity_activitytype_date_start_idx,activity_date_start_due_date_idx,activity_date_start_time_start_idx,activity_date_start_idx
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 42239
> filtered: 50.00
> Extra: Using where
> *************************** 2. row ***************************
> id: 1
> select_type: SIMPLE
> table: vtiger_crmentity
> type: eq_ref
> possible_keys: PRIMARY,crmentity_deleted_smownerid_idx
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_activity.activityid
> rows: 1
> filtered: 100.00
> Extra: Using where
> *************************** 3. row ***************************
> id: 1
> select_type: SIMPLE
> table: vtiger_groups
> type: eq_ref
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_crmentity.smownerid
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 4. row ***************************
> id: 1
> select_type: SIMPLE
> table: vtiger_users
> type: eq_ref
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_crmentity.smownerid
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 5. row ***************************
> id: 1
> select_type: SIMPLE
> table: vtiger_recurringevents
> type: ref
> possible_keys: fk_1_vtiger_recurringevents
> key: fk_1_vtiger_recurringevents
> key_len: 4
> ref: wwamh1.vtiger_crmentity.crmid
> rows: 6
> filtered: 100.00
> Extra: Using where
> 5 rows in set, 1 warning (0.00 sec)
>
> EXHIBIT B
>
> ALTER TABLE vtiger_recurringevents
> ADD INDEX recurringevents_recurringdate_idx (recurringdate);
>
> EXPLAIN EXTENDED SELECT vtiger_groups.groupname,
> vtiger_users.user_name,vtiger_crmentity.smownerid,
> vtiger_crmentity.crmid, vtiger_activity.* FROM vtiger_activity INNER
> JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
> vtiger_activity.activityid LEFT JOIN vtiger_groups ON
> vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN
> vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT
> OUTER JOIN vtiger_recurringevents ON vtiger_recurringevents.activityid
> = vtiger_activity.activityid WHERE vtiger_crmentity.deleted = 0 AND
> (vtiger_activity.activitytype not in ('Emails','Task')) AND
> (vtiger_recurringevents.recurringdate BETWEEN '2012-02-09' AND
> '2012-02-09') AND vtiger_crmentity.smownerid = 259 UNION SELECT
> vtiger_groups.groupname,
> vtiger_users.user_name,vtiger_crmentity.smownerid,
> vtiger_crmentity.crmid, vtiger_activity.* FROM vtiger_activity INNER
> JOIN vtiger_crmentity ON vtiger_crmentity.crmid =
> vtiger_activity.activityid LEFT JOIN vtiger_groups ON
> vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN
> vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT
> OUTER JOIN vtiger_recurringevents ON vtiger_recurringevents.activityid
> = vtiger_activity.activityid WHERE vtiger_crmentity.deleted = 0 AND
> (vtiger_activity.activitytype not in ('Emails','Task')) AND
> (vtiger_activity.date_start >= '2012-02-09' AND
> vtiger_activity.due_date <= '2012-02-09') AND
> vtiger_crmentity.smownerid = 259\G
>
>
> *************************** 1. row ***************************
> id: 1
> select_type: PRIMARY
> table: vtiger_recurringevents
> type: ref
> possible_keys:
> fk_1_vtiger_recurringevents,recurringevents_recurringdate_idx
> key: recurringevents_recurringdate_idx
> key_len: 4
> ref: const
> rows: 1
> filtered: 100.00
> Extra: Using where
> *************************** 2. row ***************************
> id: 1
> select_type: PRIMARY
> table: vtiger_crmentity
> type: eq_ref
> possible_keys:
> PRIMARY,crmentity_deleted_smownerid_idx,crm_ownerid_del_setype_idx
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_recurringevents.activityid
> rows: 1
> filtered: 100.00
> Extra: Using where
> *************************** 3. row ***************************
> id: 1
> select_type: PRIMARY
> table: vtiger_groups
> type: const
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 4. row ***************************
> id: 1
> select_type: PRIMARY
> table: vtiger_users
> type: const
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 5. row ***************************
> id: 1
> select_type: PRIMARY
> table: vtiger_activity
> type: eq_ref
> possible_keys:
> PRIMARY,activity_activityid_subject_idx,activity_activitytype_date_start_idx
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_recurringevents.activityid
> rows: 1
> filtered: 100.00
> Extra: Using where
> *************************** 6. row ***************************
> id: 2
> select_type: UNION
> table: vtiger_crmentity
> type: ref
> possible_keys:
> PRIMARY,crmentity_deleted_smownerid_idx,crm_ownerid_del_setype_idx
> key: crmentity_deleted_smownerid_idx
> key_len: 8
> ref: const,const
> rows: 3
> filtered: 100.00
> Extra: Using index
> *************************** 7. row ***************************
> id: 2
> select_type: UNION
> table: vtiger_groups
> type: const
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 8. row ***************************
> id: 2
> select_type: UNION
> table: vtiger_users
> type: const
> possible_keys: PRIMARY
> key: PRIMARY
> key_len: 4
> ref: const
> rows: 1
> filtered: 100.00
> Extra:
> *************************** 9. row ***************************
> id: 2
> select_type: UNION
> table: vtiger_activity
> type: eq_ref
> possible_keys:
> PRIMARY,activity_activityid_subject_idx,activity_activitytype_date_start_idx,activity_date_start_due_date_idx,activity_date_start_time_start_idx,activity_date_start_idx
> key: PRIMARY
> key_len: 4
> ref: wwamh1.vtiger_crmentity.crmid
> rows: 1
> filtered: 100.00
> Extra: Using where
> *************************** 10. row ***************************
> id: 2
> select_type: UNION
> table: vtiger_recurringevents
> type: ref
> possible_keys: fk_1_vtiger_recurringevents
> key: fk_1_vtiger_recurringevents
> key_len: 4
> ref: wwamh1.vtiger_crmentity.crmid
> rows: 6
> filtered: 100.00
> Extra: Using index
> *************************** 11. row ***************************
> id: NULL
> select_type: UNION RESULT
> table: <union1,2>
> type: ALL
> possible_keys: NULL
> key: NULL
> key_len: NULL
> ref: NULL
> rows: NULL
> filtered: NULL
> Extra:
>
--
Regards,
Asha
vtiger Team
*Connect with us on: *Twitter <http://twitter.com/#%21/vtigercrm> *I*
Facebook <http://www.facebook.com/pages/vtiger/226866697333578?sk=wall> *I*
Blog <http://blog.vtiger.com/>* I*
Wiki<http://wiki.vtiger.com/index.php/Main_Page>
*I *Forums <http://forums.vtiger.com/>*I* Website <http://vtiger.com/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20120210/028da35d/attachment-0002.html
More information about the vtigercrm-developers
mailing list