Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Timeout Issue in Production due to left join

I’m working on an application that has an issue in production where one query throws a timeout. The app uses MySQL.

SELECT
    `tags`.*,
    COUNT( ct.contact_id ) AS contacts_count 
FROM
    `tags`
    LEFT JOIN `contact_tag` AS `ct` ON `tags`.`id` = `ct`.`tag_id` 
WHERE
    `tags`.`company_id` = 1068 
    AND `tags`.`category` = 1 
    AND `tags`.`deleted_at` IS NULL 
GROUP BY
    `tags`.`id` 
ORDER BY
    `tags`.`id` DESC

so I split the query in two:

This first query works fine:

SELECT
   tags.id
FROM
    tags
WHERE
    tags.company_id = 1068 
    AND tags.category = 1 
    AND tags.deleted_at IS NULL 
GROUP BY
    tags.id
ORDER BY
    tags.id DESC
LIMIT 20

with this query I got the following tags ids:

294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287

Then this second query the first time always takes 6 seconds and then 300ms:

SELECT 
   tag_id, 
   COUNT(com_tag.contact_id) AS communications_count
FROM 
   contact_tag as com_tag
WHERE 
   tag_id IN (294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287)
GROUP BY 
   tag_id;

The result of this query is:

result set

The last query with explain we have:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE com_tag NULL ref contact_tag_contact_id_tag_id_unique, contact_tag_tag_id_foreing 4 const 25933 100.00 Using index condition
contact_tag_tag_id_foreing

contact_tag table definition is:

CREATE TABLE `contact_tag` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `contact_id` int unsigned NOT NULL,
    `tag_id` int unsigned NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `contact_tag_contact_id_tag_id_unique` (`contact_id`,`tag_id`),
    KEY `contact_tag_tag_id_foreign` (`tag_id`),
    CONSTRAINT `contact_tag_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `contact_tag_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2477082 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Tags:

2

Answers


  1. 0

    THIS is not the ANSWER !

    • Statistics
      MySQL uses statistics to optimize queries. Make sure your stats are
      up to date. you can run

      ANALYZE TABLE contact_tag;

      to update the statistics

    • Profiling
      Show us the profiling from your query with these steps and post us the results

      mysql> SET profiling = 1;

      mysql> SELECT 1; — run your query

      mysql> show profile;

      mysql> show profile ALL;

      mysql> SET profiling = 0;

    Login or Signup to reply.
  2. 0

    You haven’t included the table definitions, but from the name of this index contact_tag_contact_id_tag_id_unique I assume you have an index like this:

    UNIQUE contact_tag_contact_id_tag_id_unique (contact_id, tag_id)

    That index isn’t usable since contact_id isn’t specific. But, you have specific tag_id, so reversing the order of that index to:

    UNIQUE contact_tag_tag_id_contact_id_unique (tag_id, contact_id)

    would allow that index to be used. It’s then possible to filter out any NULL contact_id. If you don’t have NULL in contact_id, you can probably just use COUNT(*) instead, so I assume you do have some.

    Either way, you can write the query like this:

    SELECT 
   tag_id, 
   COUNT(*) AS communications_count
FROM 
   contact_tag as com_tag
WHERE 
   tag_id IN (294610,286349,286333,286332,286331,286330,268187,268175,265225,265224,265223,260136,257287) AND
   contact_id IS NOT NULL
GROUP BY 
   tag_id;

    EDIT
    All that said, you may just get faster results using a subquery
    I am assuming tags.id is unique, so you can eliminate the GROUP BY

    SELECT
    `tags`.*,
    (SELECT COUNT(*) FROM `contact_tag` WHERE `contact_tag`.`tag_id` = tags.id) AS contacts_count
FROM
    `tags`
WHERE
    `tags`.`company_id` = 1068 
    AND `tags`.`category` = 1 
    AND `tags`.`deleted_at` IS NULL 
ORDER BY
    `tags`.`id` DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search