2

This is my first question here.

I have a project written in PHP running on windows server and apache and mysql 8. There is functionality to show news by tags (many-to many).

So I have three InnoDB tables:

news (id, title ...) news_tag_assn (news_id, tag_id) tag (id, name)

I use a query to filter news containing exactly two tags:

    SELECT news.* FROM news 
    INNER JOIN news_tag_assn ON news.id = news_tag_assn.news_id 
    INNER JOIN tag ON news_tag_assn.tag_id = tag.id 
    WHERE  (tag.name IN ('Corona', 'Egypt')) AND (news.category_id <> 14) AND (news.visible=1)
    GROUP BY news.id, news.category_id, news.visible 
    HAVING COUNT(*) = 2 
    ORDER BY news.created DESC, news.updated DESC LIMIT 5

I have created some indexes:

For news_tag_assn table:

  • idx_news_tag_assn (news_id, tag_id)
  • idx_tag_news_assn (tag_id, news_id)

For tag table:

  • tag_id_name (id, name)

For news table:

  • idx_news_visible_tags (id, category_id, visible)
  • idx_cat_visible (category_id, visible)

After creating indexes query become faster (0.144ms vs > 1 sec.)

Another issue is counting news for pagination:

    SELECT COUNT(*) FROM (SELECT news.* FROM news 
    INNER JOIN news_tag_assn ON news.id = news_tag_assn.news_id 
    INNER JOIN tag ON news_tag_assn.tag_id = tag.id 
    WHERE  (tag.name IN ('Corona', 'Egypt')) AND (news.category_id <> 14) AND (news.visible=1)
    GROUP BY news.id, news.category_id, news.visible 
    HAVING COUNT(*) = 2 ) c

This query is slow than first in 2 times.

But i have a doubt that i have made all correctly. Maybe someone can advice something how can I make this query more fast.

UPDATE

SHOW CREATE TABLE news:

CREATE TABLE `news` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `alias` text,
  `short_description` varchar(200) DEFAULT NULL,
  `description` varchar(765) DEFAULT NULL,
  `content` longtext COMMENT,
  `label` varchar(255) DEFAULT NULL,
  `category_id` int NOT NULL COMMENT,
  `thumb` varchar(255) DEFAULT NULL,
  `type` int DEFAULT '1',
  `link` varchar(255) DEFAULT NULL,
  `priority` int DEFAULT '0',
  `creator_id` int DEFAULT NULL,
  `layout` varchar(255) DEFAULT NULL,
  `internal_layout` varchar(255) DEFAULT NULL,
  `created` int DEFAULT NULL,
  `updated` int DEFAULT NULL,
  `visible` int NOT NULL DEFAULT '1',
  `custom_bundles` int DEFAULT '0',
  PRIMARY KEY (`id`,`category_id`,`visible`) USING BTREE,
  KEY `idx_news_visible_for_tags` (`id`,`category_id`,`visible`),
  KEY `idx_cat_visible` (`category_id`,`visible`),
  KEY `idx_date` (`created`,`updated`)
) ENGINE=InnoDB AUTO_INCREMENT=22269 DEFAULT CHARSET=utf8mb3

SHOW CREATE TABLE news_tag_assn:

CREATE TABLE `news_tag_assn` (
  `news_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`tag_id`,`news_id`),
  KEY `idx_news_tag_assn` (`news_id`,`tag_id`) USING BTREE,
  KEY `idx_tag_news_assn` (`tag_id`,`news_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

SHOW CREATE TABLE tag:

CREATE TABLE `tag` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL,
  `group_id` int DEFAULT NULL,
  `frequency` int DEFAULT '0',
  `visible` int DEFAULT '0',
  PRIMARY KEY (`id`,`name`) USING BTREE,
  KEY `tag_id` (`id`) USING BTREE,
  KEY `tag_id_name` (`id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=156 DEFAULT CHARSET=utf8mb3
2
  • Please consider reading this advice
    – mustaccio
    Commented Feb 4, 2022 at 12:34
  • 1
    Short version of above: use EXPLAIN and consider using "USING INDEX ..." to use a specific index if MySQL is being inefficient Commented Feb 4, 2022 at 12:50

1 Answer 1

1
  • Your goal is to page through articles that are tagged with both Corona in Egypt?
  • Please provide SHOW CREATE TABLE, not verbiage. With the SHOW, I can see whether you have the optimal indexes. (Hint: Many-to-many )
  • I have found that it is better to simply use the tag string instead of normalizing it to an ID. This would eliminate the need for a many-to-many table, which is part of the performance problem.
  • What table is updated in? I can't optimize a query without knowing what table each column is in.
  • After taking care of those, we can revisit whether the HAVING COUNT(*) = 2 pattern is optimal.
  • You mentioned "pagination", but I don't see OFFSET. Tip: Pagination

Tip: Since the PRIMARY KEY of a table is an index (and UNIQUE), there is not need to have an index the same as the PK. (Example: idx_news_visible_for_tags.)

Toss both of the KEYs of tags (but keep the PRIMARY KEY) and add INDEX(name). This may be the best performance improvement.

3
  • Thank you! I've updated question. I don't understand only - "What table is updated in? I can't optimize a query without knowing what table each column is in." Commented Feb 7, 2022 at 6:33
  • @demogorgorn - I can now see that the column updated lives in the table news. I'll update your query.
    – Rick James
    Commented Feb 7, 2022 at 19:00
  • 1
    And I added some tips to my Answer.
    – Rick James
    Commented Feb 7, 2022 at 19:07

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.