Ordering By a Global Count Causes Filesort

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

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.


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`)

try this –

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

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?

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