[Vtigercrm-developers] calendar performance bottleneck
Adam Heinz
amh at metricwise.net
Thu Feb 9 13:32:57 PST 2012
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:
More information about the vtigercrm-developers
mailing list