Issue 280 Email Lookups Scanned The Whole Table - edmozley/freeitsm GitHub Wiki
Collecting mail and opening conversations scanned every email
Found: while answering #262, raised as #280, November 2026 · Module: Tickets · Fixed in: the release after 4.11.0 (changelog #2761) · Run Database Verify after upgrading
What you would have noticed
Nothing at first. On a new install the emails table is small and every lookup is instant. But two things FreeITSM does all day got slower the more ticket history you have:
- Collecting mail. Every message a mailbox check finds is first looked up to see whether it was imported already.
- Opening a ticket. The conversation is every email on that ticket.
Neither depended on how big the mailbox is - only on how many emails FreeITSM has ever stored.
What was actually wrong
Both lookups search the emails table by a column that had no index:
| Lookup | Where | Query |
|---|---|---|
| Already imported? | api/tickets/check_mailbox_email.php |
WHERE exchange_message_id = ? |
| A ticket's conversation | the reading pane, the thread, the case report | WHERE ticket_id = ? |
emails had only its primary key (and, on installs whose foreign keys were applied, indexes for those foreign keys - none of which help here). Without an index, MySQL answers WHERE exchange_message_id = ? by reading every row - EXPLAIN shows type: ALL. That is fine at 500 emails and a real cost at 500,000.
It came to light answering #262's question about mailboxes with 15,000 messages: the folder size turned out not to matter (the mail server answers "which are unread" from its own index), but FreeITSM's own duplicate check did.
The fix
Two indexes, in database/freeitsm.sql:
KEY `idx_emails_exchange_message_id` (`exchange_message_id`),
KEY `idx_emails_ticket` (`ticket_id`),
includes/db_verify_indexes.php is regenerated from it (php scripts/gen_db_verify_indexes.php), so Database Verify adds them to an existing install - it reports Restored missing index idx_emails_exchange_message_id and idx_emails_ticket. No code changed: the same queries simply use them.
On a large emails table adding an index takes a moment; MySQL 8 builds it online, so the service desk keeps working while it runs.
How it was proved
On a throwaway install, before and after Database Verify:
| Query | Before | After |
|---|---|---|
SELECT id, ticket_id FROM emails WHERE exchange_message_id = ? LIMIT 1 |
type: ALL (every row) |
type: ref, key idx_emails_exchange_message_id |
SELECT id FROM emails WHERE ticket_id = ? |
type: ALL |
type: ref, key idx_emails_ticket |
tests/emails-lookup-indexes.php checks that both indexes are in freeitsm.sql and in Database Verify's list, and - given the test database - that EXPLAIN shows MySQL using them. 6 checks.
What this means for you
- Run System → Database Verification after upgrading. That is what adds the indexes to an existing install.
- Collecting mail and opening tickets stay quick however much history builds up.