FreeScout MySQL/MariaDB database search speed optimization

indexes.sql

CREATE INDEX idx_conversations_last_reply_at ON conversations(last_reply_at DESC);
ALTER TABLE conversations ADD FULLTEXT ft_conversations_search (subject, customer_email);
ALTER TABLE customers ADD FULLTEXT ft_customers_search (first_name, last_name);
ALTER TABLE threads ADD FULLTEXT ft_threads_search (body, `from`, `to`, cc, bcc);
CREATE INDEX idx_conversations_mailbox_filter ON conversations(mailbox_id, last_reply_at DESC);
CREATE INDEX idx_conversations_covering ON conversations(mailbox_id, customer_id, subject(100), customer_email(100), last_reply_at DESC);

-- Drop the useless mailbox indexes
DROP INDEX conversations_mailbox_id_customer_id_index ON conversations;
DROP INDEX idx_conversations_covering ON conversations;

-- Create indexes optimized for full-text search
CREATE INDEX idx_conversations_subject ON conversations(subject(100));
CREATE INDEX idx_conversations_email ON conversations(customer_email(100));
CREATE INDEX idx_customers_names ON customers(first_name(50), last_name(50));
CREATE INDEX idx_threads_body ON threads(body(200));
CREATE INDEX idx_threads_from ON threads(`from`(100));
CREATE INDEX idx_threads_to ON threads(`to`(100));
CREATE INDEX idx_threads_cc ON threads(cc(100));
CREATE INDEX idx_threads_bcc ON threads(bcc(100));