Query is faster with additional JOIN SELECT COUNT(DISTINCT User_id) FROM Session 
GROUP BY Floor_id;




SELECT COUNT(DISTINCT User_id) FROM Session 
JOIN Floor ON Floor.id = Floor_id
GROUP BY Floor_id;




The second query runs 20x faster than the first query.

Floor_id is a FK to Floor.id.

Why is this the case and how should we know when we can make use of JOINs to speed up queries?

CREATE TABLE `Session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `startTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  `ssid` varchar(45) DEFAULT NULL,
  `AccessPoint_id` int(11) DEFAULT NULL,
  `User_id` int(11) NOT NULL,
  `Floor_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique` (`User_id`,`AccessPoint_id`,`startTime`),
  KEY `fk_session_AccessPoint1_idx` (`AccessPoint_id`),
  KEY `fk_session_User1_idx` (`User_id`),
  KEY `time` (`startTime`,`endTime`),
  KEY `fk_Session_Floor1_idx` (`Floor_id`),
  CONSTRAINT `fk_Session_Floor1` FOREIGN KEY (`Floor_id`) REFERENCES `Floor` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_AccessPoint1` FOREIGN KEY (`AccessPoint_id`) REFERENCES `AccessPoint` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_session_User1` FOREIGN KEY (`User_id`) REFERENCES `User` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1099568396 DEFAULT CHARSET=utf8
You want faster?  Add INDEX(floor_id, user_id).