MySQL optimize count query

Question

I've got a question about MySQL performance. These are my tables:

(about 140.000 records)

CREATE TABLE IF NOT EXISTS `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `label` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `intro` text COLLATE utf8_unicode_ci NOT NULL,
  `content` text COLLATE utf8_unicode_ci NOT NULL,
  `date` int(11) NOT NULL,
  `active` int(1) NOT NULL,
  `language_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `indexed` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=132911 ;

(about 400.000 records)

CREATE TABLE IF NOT EXISTS `article_category` (
  `article_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

RUNNING THIS COUNT QUERY:

SELECT SQL_NO_CACHE COUNT(id) as total 
FROM (`article`) 
LEFT JOIN `article_category` ON `article_category`.`article_id` = `article`.`id` 
WHERE `article`.`language_id` = 1 
AND `article_category`.`category_id` = '<catid>'

This query takes a lot of resources, so I am wondering how to optimize this query. After executing it's beeing cached, so after the first run I am fine.

RUNNING THE EXPLAIN FUNCTION:

enter image description here

AFTER CREATING AN INDEX:

ALTER TABLE `article_category` ADD INDEX ( `article_id` , `category_id` ) ;

enter image description here

After adding indexes and changing LEFT JOIN to JOIN the query runs alot faster! Thanks for these fast replys :)

QUERY I USE NOW (I removed the language_id because it was not that neccesary):

SELECT COUNT(id) as total 
FROM (`article`) 
JOIN `article_category` ON `article_category`.`article_id` = `article`.`id` 
AND `article_category`.`category_id` = '<catid>'

I've read something about forcing an index, but I think thats not neccesary anymore because the tables are already indexed, right?

Thanks alot!

Martijn

Either you shouldn't do a LEFT JOIN, or you need to test category_id = <catid> in the ON clause. Otherwise you'll filter out the articles with no matches in article_categories. — Barmar
– Barmar, Commented Apr 23, 2013 at 11:50
Have you tried creating the other index which I have mentioned... Is this making any difference ? — Meherzad
– Meherzad, Commented Apr 23, 2013 at 13:02

Meherzad · Accepted Answer · 2013-04-23 11:56:07Z

2

You haven't created necessary index on the table

Table article_category - Create a compound index on (article_id, category_id)

Table article -Create a compound index on (id, language_id)

If this doesn't help post the explain statement.

answered Apr 23, 2013 at 11:56

Meherzad

8,5611 gold badge33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2013-04-23 11:55:01Z

1

The columns used in a JOIN condition should have an index, so you need to index article_id.

answered Apr 23, 2013 at 11:55

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

MySQL optimize count query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related