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