Slow query in a view, how to improve it (similar to putting indexes?)

[Cyril N.]

Hi everyone!

I made quite a complex View on my MySQL database, and I realized that some queries are slow. My first idea was to add indexes but it's not possible to do on a view so I'm lost on how to improve the performance of my query.

I'll share here my View query, and I'll do my best to explain it, then I'll share my database structure (stripped down to what is needed only for the view).

CREATE VIEW conversations_search AS (
    SELECT a.id, a.organization_id, a.status, a.way, a.agent_id, a.subject, a.snooze_until, a.channel_id, a.source, a.contact_id, a.name, a.email, a.created, a.message, a.category, a.is_personal, a.is_customer, a.last_update,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d'), a.is_customer, DATE_FORMAT(a.last_update, '%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS priority_sort,
        CONCAT(DATE_FORMAT(a.last_update, '%Y%m%d%H%i%S'), RIGHT(LPAD(a.id, 6, '0'), 6)) AS date_sort,
        CONCAT(RIGHT(LPAD(CAST((UTC_TIMESTAMP() - a.last_update) AS UNSIGNED) * 100, 12, '0'), 12), RIGHT(LPAD(a.id, 6, '0'), 6)) AS longest_sort
    FROM (
        SELECT c.id, c.organization_id, c.status, e.way, c.agent_id, c.subject AS subject, c.snooze_until, c.channel_id, c.source, c.contact_id, cn.name, cn.email, e.created, e.message, e.category, c.is_personal,
            IF(pc.customer_id IS NULL, 0, 1) AS is_customer,
            (
                IFNULL(
                    (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'IN' ORDER BY created DESC LIMIT 1),
                    IFNULL(
                        (SELECT created FROM messages WHERE conversation_id = c.id AND way = 'OUT' ORDER BY created DESC LIMIT 1),
                        (SELECT created FROM message_drafts WHERE conversation_id = c.id)
                    )
                )
            ) AS last_update
        FROM (
            SELECT m.way, m.created, m.message, m.conversation_id, 'MESSAGE' AS category FROM messages m WHERE `status` != 'DRAFT'
            UNION
            SELECT NULL AS way, n.created AS created, n.message, n.conversation_id, 'NOTE' AS category FROM conversation_notes n WHERE `status` != 'DRAFT'
        ) AS e LEFT JOIN conversations c ON c.id = e.conversation_id LEFT JOIN contacts cn ON cn.id = c.contact_id
            LEFT JOIN processor_customers pc ON pc.contact_id = c.contact_id
    ) AS a
);

Explaination: This view loads a few columns related to a tickets, including:

* the ticket id
* the organization_id to which the ticket is affected
* The status (closed, opened, etc)
* the way the last message was sent (received or sent)
* The assigned agent id
* The subject
* The snooze time (if any)
* the channel id (like [e-mail])
* The contact id (From email)
* The contact's name
* The contact's email
* The ticket's creation date
* the last message content
* the category (if the last entry ss a message or a note)
* If the received message is personal (the channel where the email was sent is personal, like [e-mail], or open to anyone in the team, like support@..)
* If the contact is a customer
* The last time the ticket was updated
* A column that is used to sort by priority
* A column to sort by date
* A column to sort by waiting longer

I'm definitely not a DB expert, so maybe there a ton of improvements available already on that query, but what I realized is that in production, a simple query like :

> SELECT COUNT(id) FROM conversations_search WHERE organization_id = X;
Takes about 2.5 seconds to run. I tried with a few different IDs, and the result is between 900 and 18000, but still takes around 2.5 seconds.

Clearly, its missing some optimisations...

Here's my DB structure (I removed some useless columns for the sake of simplicity here):

CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `created` datetime NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11181 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(250) DEFAULT NULL,
  `status` varchar(15) NOT NULL,
  `source` varchar(15) NOT NULL,
  `created` datetime(6) NOT NULL,
  `snooze_until` datetime DEFAULT NULL,
  `channel_id` bigint(20) unsigned DEFAULT NULL,
  `organization_id` int(11) NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `is_personal` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `channel_id` (`channel_id`),
  KEY `contact_id` (`contact_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `conversations_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversations_ibfk_2` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`),
  CONSTRAINT `conversations_ibfk_3` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `conversations_ibfk_4` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17502 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `messages` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message` mediumtext DEFAULT NULL,
  `way` varchar(3) NOT NULL,
  `created` datetime(6) NOT NULL,
  `last_update` datetime NOT NULL,
  `status` varchar(20) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned DEFAULT NULL,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `contact_id` (`contact_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`),
  CONSTRAINT `messages_ibfk_3` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27845 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `conversation_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `agent_id` bigint(20) unsigned DEFAULT NULL,
  `message` mediumtext DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `created` datetime(6) NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `conversation_notes_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `conversation_notes_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `message_drafts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `content` mediumtext DEFAULT NULL,
  `created` datetime(6) NOT NULL,
  `last_updated` datetime NOT NULL,
  `conversation_id` bigint(20) unsigned NOT NULL,
  `agent_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `agent_id` (`agent_id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `message_drafts_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`),
  CONSTRAINT `message_drafts_ibfk_2` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=653 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `agents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `email` varchar(250) NOT NULL,
  `organization_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_agents_email` (`email`),
  UNIQUE KEY `email` (`email`,`organization_id`),
  KEY `organization_id` (`organization_id`),
  CONSTRAINT `agents_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=679 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(250) NOT NULL,
  `slug` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_organizations_slug` (`slug`),
) ENGINE=InnoDB AUTO_INCREMENT=647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

How can I improve the query above?
(The view contains these values because I can search by contact name or email, by status, by agent, by way (in/out), by channel, etc. All the columns can be used when searching.

Thank you for your help!