Ordering By a Global Count Causes Filesort

Databases
1

We are trying to generate global leaderboards for our users to acknowledge the top performers. In our game, one leaderboard is for total items used. Items are used by users’ pets. Here’s the query:


SELECT 	u.username,
	(SELECT COUNT(*) FROM items_used WHERE pet_id IN (SELECT id FROM pets WHERE user_id = u.id)) AS count
FROM users u
ORDER BY count DESC
LIMIT 50;

Explain reveals:


id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	u	index	NULL	username	32	NULL	34099	Using index; Using filesort
2	DEPENDENT SUBQUERY	items_used	index	NULL	pet_id	8	NULL	15407	Using where; Using index
3	DEPENDENT SUBQUERY	pets	unique_subquery	PRIMARY,user_id	PRIMARY	8	func	1	Using where

What this query does is:

[list][]General select from the users table
[]Initially gets all of the user’s pet IDs in SELECT id FROM pets WHERE user_id = u.id
[][FONT=Courier New]SELECT COUNT() FROM items_used […][/FONT] then gets the total items used by the user’s pets[/list]
The problem is the ORDER BY clause, which requires the query to generate a count for every single user in order to sort. How do I avoid a filesort like this? Is the only way to avoid this by running a cron job that updates a single “total_items_used” column in a users table for this count? Thanks in advanced.

items_used:


CREATE TABLE `items_used` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pet_id` bigint(20) NOT NULL,
  `item_id` bigint(20) NOT NULL,
  `time_used` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `pet_id` (`pet_id`),
  KEY `item_id` (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15236 DEFAULT CHARSET=utf8;
2

try this –

SELECT users.username
     , COUNT(*) AS items
  FROM users 
INNER
  JOIN pets 
    ON pets.user_id = users.id
INNER
  JOIN items_used
    ON items_used.pet_id = pets.id     
GROUP
    BY users.username
ORDER 
    BY items DESC LIMIT 50;
3

Bravo! It works.

Curious, but in the context of JOINs, does COUNT(*) count the rows returned from last joined table in the query? How does hierarchy work in that concept?

4

yes, it counts joined rows

A 1 11
A 1 22
A 1 33
A 2 90
A 2 37
B 5 22
B 5 33
B 6 44