1

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 [email protected])
  • 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 [email protected], 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!

2
  • Please explain the intent of the CONCAT(RIGHT(LPAD(CAST...; there is probably a simpler way to do it. TO_SECONDS() may help simplify it.
    – Rick James
    Sep 16, 2023 at 0:00
  • It's to allow ordering by different ways and the CONCAT is because it allows to sort with members being paid customers first - per day.
    – Cyril N.
    Sep 18, 2023 at 7:32

2 Answers 2

1
+50

You are extracting a lot of things in a single view, but I can suggest two optimizations:

  1. Since organization_id and contact_id are declared NOT NULL in conversations and there is a constraints for both, you can convert all those LEFT JOINs in simple JOINs. That could help the optimizer choose a better plan.
  2. The subqueries SELECT created FROM messages WHERE conversation_id = c.id AND ... are executed for every single row. These can be otpimized substituting the KEY conversation_id (conversation_id) in messages with KEY conversation_id_way_created (conversation_id, way, created DESC) which includes all the fields necessary to the query in the right order.

A lot of time is expended in calculating last_update. For every message or conversation_note you are going to execute one, two or three selects just to get the last_update value. This block could be simplified, to just two SELECTs in the following way:

    IFNULL(
            (SELECT created FROM messages WHERE conversation_id = c.id ORDER BY way ASC, created DESC LIMIT 1),
            (SELECT created FROM message_drafts WHERE conversation_id = c.id)
            ) AS last_update

Also, the last_update value is calulated for every message, but it depends only on the conversation_id, which is the same for multiple messages in the same conversation. You can move this calculation to the conversation level in this way:

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,
    c.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 JOIN 
(SELECT conversations.*, 
    IFNULL(
            (SELECT created FROM messages WHERE conversation_id = conversations.id ORDER BY way ASC, created DESC LIMIT 1),
            (SELECT created FROM message_drafts WHERE conversation_id = conversations.id)
            ) AS last_update 
    FROM conversations
) AS c ON c.id = e.conversation_id JOIN contacts cn ON cn.id = c.contact_id
    JOIN processor_customers pc ON pc.contact_id = c.contact_id
) AS a
5
  • Thank you so much for your suggestion! About the LEFT JOINs becoming simple JOINs, do I also do it for "LEFT JOIN processor_customers" ? As for your second suggestion, I'm not sure to understand, could you clarify a bit more please ?
    – Cyril N.
    Aug 31, 2023 at 9:17
  • Or maybe you mean creating a KEY constraint that, instead of matching conversation_id, would match conversation_id, way, created DESC? Should I keep the simple Key conversation_id ?
    – Cyril N.
    Aug 31, 2023 at 13:03
  • I tried your suggestions but I'm still around 2.2 seconds to execute the SELECT COUNT(id) FROM conversations_search WHERE organization_id = 3;
    – Cyril N.
    Aug 31, 2023 at 13:25
  • @CyrilN. If you create the new KEY constraint, you can remove the simple Key conversation_id from the messages table. I added some suggestions to optimize the way you calculate last_update
    – Andrea B.
    Sep 15, 2023 at 10:52
  • Thank you. This helped.
    – Cyril N.
    Sep 18, 2023 at 7:33
1

Indexes:

messages: INDEX(conversation_id, way, created)
message_drafts: INDEX(conversation_id,created)
pc:  INDEX(contact_id,  customer_id)

Try to find a way that avoids fetching messages.created twice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.