3
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
  • This queries are not equivalent. So the compare makes no sense. – Akina Apr 15 at 5:13
  • @Akina they gave the same result – Avery235 Apr 15 at 5:43
  • 1
    The 20x may be due to caching. Time each variant again. – Rick James Apr 28 at 13:35
  • @RickJames I did, it isn't due to caching. – Avery235 May 1 at 3:44
  • @Akina they look like they are equivalent, since here is the FK. Not sure if MySQL optimizer can understand they are though. – ypercubeᵀᴹ May 2 at 9:23
1

You want faster? Add INDEX(floor_id, user_id).

  • Doesn't make a difference. Can you elaborate? – Avery235 Apr 28 at 12:49
  • @Avery235 - Please provide SHOW CREATE TABLE Session – Rick James May 1 at 3:52
  • Added the result to the post. – Avery235 May 2 at 9:00
  • @Avery235 please add the plans and the times, after the index on (floor_id, user_id) has been added, too. – ypercubeᵀᴹ May 2 at 9:25
0

As stated both query gives same answer. So i am also wondering how that worked.

After few hrs of research I found something, which i am explaining here.

1st Query

  • Full Index Scan {fk_Session_Floor1}
  • Group after that which dose sorting entire table for group by field
  • Apply Count function with DISTINCT User_id

2nd Query

  • Full Index Scan {fk_Session_Floor1}
  • Unique lookup from Floor table {Join happening}
  • Filesort default for any kind of join so that rows are already sorted with key(s)
  • group by on sorted row
  • Apply Count function with DISTINCT User_id

8.8.2 EXPLAIN Output Format for any reference where it shows the following..

  1. Distinct (JSON property: distinct)

MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.

  1. Using filesort (JSON property: using_filesort)

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order.

Also See Section 8.2.1.14

Also as we all know that if records are already sorted before grouping then group by don't need to do any kind of sorting. it can directly apply mapper, reducer or aggregation function.

So here join make that little easy however it does not reduce number of rows. but it also makes all rows sorted by key by default which previously not doing it.

This is why your query is lot more faster.

Hope this is what you are looking for.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.