Anyway to improve performance of SQL query to find rows with order by tag match count

Server: 10.3.27-MariaDB-log
I have table of items and many-to-many table of item tags. In the search form I list all the tags that I want to find and expect to receive the ordered list of item ids with tags count in descending order. Nothing unusual.

CREATE TABLE `item` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2283235
;

Item table contains more than 2M rows.

CREATE TABLE `item_tag` (
    `item_id` INT(10) UNSIGNED NOT NULL,
    `tag_id` INT(10) UNSIGNED NOT NULL,
    INDEX `fk_item_tag_tag_idx` (`tag_id`),
    INDEX `fk_item_tag_item_idx` (`item_id`),
    INDEX `tid_iid_idx` (`tag_id`, `item_id`),
    INDEX `iid_tid_idx` (`item_id`, `tag_id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Item_tag table as of now contains more than 60M rows.

After some struggling to find best query I ended up with 2 solutions which works almost the same in terms of speed:

SELECT 
    i.`id`, 
    counter.cnt
FROM `item` i
RIGHT JOIN (
    SELECT
        it.item_id,
        COUNT(it.item_id) AS cnt
    FROM 
        item_tag it
    WHERE
        it.tag_id IN (3733, 5203, 5202, 1234) << --- test TAG_IDs
    GROUP BY 
        it.item_id
    ORDER BY NULL
) counter ON counter.item_id = i.id
ORDER BY
    counter.cnt DESC, i.id DESC
LIMIT 50;

executed in 4,118 sec.

and

SELECT
    it.item_id,
    COUNT(*) AS cnt
FROM item_tag it
INNER JOIN item i ON i.id = it.item_id 
WHERE
    it.tag_id IN (3733, 5203, 5202, 1234)
GROUP BY
    it.item_id
ORDER BY
    cnt DESC,
    it.item_id DESC
LIMIT 50;

executed in 3,386 sec.

Execution time of the queries largely depends on how frequent specified tag is. Previous times were for the following tags and counts:

| tag_id   | counter (number of items) |
| -------- | ------- |
| 3733     | 457357  |
| 5203     | 14300   |
| 5202     | 13803   |
| 1234     | 0       |

But if I repeat those queries for more popular tags my query execution time rocket to the 40-50 seconds:

| tag_id   | counter (number of items) |
| -------- | ------- |
| 3927     | 497732  |
| 4189     | 472916  |
| 3714     | 505325  |
| 3702     | 369115  |

1st type query explain:

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
--|------|-----|-----|-------|---|-------|--|---|------
1|PRIMARY|<derived2>|ALL|\N|\N|\N|\N|3271866|Using temporary| Using filesort
1|PRIMARY|i|eq_ref|PRIMARY|PRIMARY|4|counter.item_id|1|
2|DERIVED|it|range|fk_item_tag_tag_idx,tid_iid_idx|tid_iid_idx|4|\N|3271866|Using where| Using index| Using temporary

2nd type query explain:

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
--|------|-----|-----|-------|---|-------|--|---|------
1|SIMPLE|it|range|fk_item_tag_tag_idx,fk_item_tag_item_idx,tid_iid_idx,pid_kid_idx|tid_iid_idx|4|\N|3271866|Using where| Using index| Using temporary| Using filesort
1|SIMPLE|i|eq_ref|PRIMARY|PRIMARY|4|lm2.it.item_id|1|Using index

As you may have guessed :) this time is unacceptable to me. I wonder what type of optimization can be done to reduce query execution time?

UPDATE 2
There is another table 'TAG':

CREATE TABLE `tag` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `tag` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `last_assigned` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id_UNIQUE` (`id`),
    UNIQUE INDEX `tag_UNIQUE` (`tag`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=372469;

I have tested additional queries from ysth's answer and execution time dropped from 44.563 seconds -> 38.922 -> 33.931.

Anyway, it seems like there is no way to speed up execution time until I narrow down the search by adding new conditions. I can hardly think of a better way to shrink the search scope.

• Limit the search query by time column (i.e. show me last year items)
• Does not allow user to search for several most popular tags at once
• ... ?