On indexes: yes, a few would help a lot based on the query patterns I used.
Most useful indexes:
db.zd_tickets.createIndex({ assignee: 1, status: 1, updated_ts: -1 })
For “my current/open-ish tickets” and sorted current work.
db.zd_tickets.createIndex({ assignee: 1, updated_ts: -1 })
For historical --include-closed --updated-since ... searches.
db.zd_tickets.createIndex({ assignee: 1, latest_comment_added_ts: -1 })
For finding recently-commented or not-recently-commented assigned tickets.
db.zd_tickets.createIndex({ assignee: 1, created_ts: -1 })
For historical review by creation date.
For exact ticket lookup, _id is already indexed by default, so no need there.
The current script does regex over many fields:
Regex across many fields is expensive. I’d add a text index or Atlas Search index if this collection is in Atlas.
Simple MongoDB text index:
db.zd_tickets.createIndex({
raw_subject: "text",
subj: "text",
desc: "text",
requester: "text",
requestor: "text",
submitter: "text",
assignee: "text",
cc: "text",
sa_email: "text",
spec_product_area: "text",
spec_request_typ: "text"
})
If using Atlas Search, I’d prefer an Atlas Search index for these fields, because it will be more flexible for customer/account matching than MongoDB text search.
This one matters a lot because every ticket comment lookup used:
{ "importMeta.ticket_id": tid }
with sorting by either comment order or date.
Recommended:
db.zd_comments.createIndex({
"importMeta.ticket_id": 1,
"importMeta.comment_dt": -1
})
For latest-comments lookups.
db.zd_comments.createIndex({
"importMeta.ticket_id": 1,
"importMeta.commen_order": 1,
"importMeta.comment_dt": 1
})
For earliest/comments-in-order lookups.
If filtering public-only often:
db.zd_comments.createIndex({
"importMeta.ticket_id": 1,
public: 1,
"importMeta.comment_dt": -1
})
Also useful for “did Pat comment?” style checks:
db.zd_comments.createIndex({
author_email: 1,
"importMeta.comment_dt": -1,
"importMeta.ticket_id": 1
})
That would make it efficient to search Pat’s recent comments directly instead of loading comments ticket-by-ticket.
The calendar helper queries patterns like:
{
who: "pat.wendorf",
title: { $exists: true, $nin: [null, ""] },
das: { $gte: "...", $lte: "..." },
cf: true
}
and sorts by start.
Recommended:
db.availability.createIndex({
who: 1,
cf: 1,
das: 1,
start: 1
})
For customer-facing range queries.
For normal schedule/date queries:
db.availability.createIndex({
who: 1,
das: 1,
start: 1
})
For text/customer/event lookups across calendar events, the current helper searches title/participants/organizer/account/ticket subjects. If Atlas Search is available, I’d use an Atlas Search index. Otherwise:
db.availability.createIndex({
title: "text",
"details.organizer.email": "text",
"details.participants.email": "text",
"account.parent.name": "text",
"account.matching_accounts.name": "text",
"tickets.raw_subject": "text"
})
For ticket-linked calendar matching:
db.availability.createIndex({
"tickets._id": 1,
who: 1,
start: -1
})
For account-based matching:
db.availability.createIndex({
"account.parent.name": 1,
who: 1,
start: -1
})
If you only add a few, I’d do these first:
db.zd_comments.createIndex({ "importMeta.ticket_id": 1, "importMeta.comment_dt": -1 })
db.zd_comments.createIndex({ author_email: 1, "importMeta.comment_dt": -1, "importMeta.ticket_id": 1 })
db.zd_tickets.createIndex({ assignee: 1, status: 1, updated_ts: -1 })
db.zd_tickets.createIndex({ assignee: 1, updated_ts: -1 })
db.availability.createIndex({ who: 1, cf: 1, das: 1, start: 1 })
Those would directly speed up the calendar → ticket → latest comments workflow we just did.