CODEWITHSUNDEEP
mysqloptimizationjoinquery-optimizationsubquery
Nicola Moretti
Nicola Moretti

Reputation: 3

Mysql subquery optimization - count

i have these subqueries in a main query used to fetch some events:

SELECT [...],
(SELECT COUNT(*) FROM WEventUser WHERE WEventUser.eID=e.eID AND favorited=1) as numfavorited,
(SELECT COUNT(*) FROM WEventUser WHERE WEventUser.eID=e.eID AND subscribed=1) as numsubscribed,
(SELECT COUNT(*) FROM WEventUser WHERE eID=e.eID AND WEventUser.uID=2 AND favorited=1) as favorited,
(SELECT COUNT(*) FROM WEventUser WHERE eID=e.eID AND WEventUser.uID=2 AND subscribed=1) as subscribed,
[...] WHERE...etc.

structure of WEventUser is quite simple

CREATE TABLE IF NOT EXISTS `WEventUser` (
  `eID` int(10) unsigned NOT NULL auto_increment,
  `uID` int(10) unsigned NOT NULL,
  `favorited` int(1) unsigned default '0',
  `subscribed` int(1) unsigned default '0',
  PRIMARY KEY  (`eID`,`uID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

These subqueries are really expensive. Can you help me finding an alternative (like a single join)?

Thanks in advance!

EDIT: I'm selecting from a main WEvents table that is:

CREATE TABLE IF NOT EXISTS `wevents` (
`eID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `uID` int(10) unsigned DEFAULT NULL,
 `ecID` int(10) unsigned NOT NULL,
 `eName` varchar(64) NOT NULL,
 `eDescription` longtext,
 `eIsActive` varchar(1) NOT NULL DEFAULT '0',
 `eIsValidated` tinyint(4) NOT NULL DEFAULT '-1',
 `eDateAdded` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `eDateModified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`eID`,`ecID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 509

Answers (1)

Devart
Devart

Reputation: 122032

You shouldn't use a subqueries, it is enough to count values in the COUNT function, e.g. -

SELECT [...],
COUNT(IF(wu.favorited = 1, 1, NULL)) as numfavorited,
COUNT(IF(wu.subscribed = 1, 1, NULL)) as numsubscribed,
COUNT(IF(wu.uID=2 AND wu.favorited=1, 1, NULL)) as favorited,
COUNT(IF(wu.uID=2 AND wu.favorited = 1, 1, NULL)) as subscribed,
[...]
FROM
  WEventUser wu
WHERE...etc.

You can easily use this one if you want to join WEventUser with another table.

Upvotes: 2

Related Questions