Using temporary; Using filesort

[neoform]

Given this query:

 

SELECT
			profiles.user_id,
			profiles.status, 
			profiles.category_id, 
			users.username,
			categories.name category_name,
			categories.url_name category_url_name,
			last_on,

			(
				12434.4237087 * 
				sqrt(
					(info.latitude - 40.7519) 
					* 
					(info.latitude - 40.7519) 
					+ 
					cos(info.latitude / 57.29578) 
					* 
					cos(40.7519 / 57.29578) 
					* 
					(info.longitude - -73.9954) 
					* 
					(info.longitude - -73.9954)
				) / 180
			) distance

		FROM
			phpneoform_profiles_users profiles
		INNER JOIN
			dating_user_info info
		ON
			profiles.user_id = info.user_id
		INNER JOIN
			system_users users
		ON
			users.id = profiles.user_id
		INNER JOIN
			phpneoform_profiles_categories categories
		ON
			categories.id = profiles.category_id
		INNER JOIN
			system_users_sessions sessions
		ON
			users.id = sessions.user_id
		WHERE
			profiles.status = 'approved'
AND 
users.status = 'active'
AND 
info.birthday BETWEEN '1978-10-17' AND '1986-10-17'
AND 
info.seeking = 'male'
AND 
info.gender = 'female'
		HAVING
			distance < '500'
		ORDER BY 
			sessions.last_on DESC 
		LIMIT 100

 

and this description:

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	categories	ALL	PRIMARY				3	Using temporary; Using filesort
1	SIMPLE	profiles	ref	PRIMARY,status1,status2	status2	1	const	224	Using where; Using index
1	SIMPLE	users	eq_ref	PRIMARY,userid	PRIMARY	3	core.profiles.user_id	1	Using where
1	SIMPLE	info	eq_ref	PRIMARY,info	PRIMARY	3	core.profiles.user_id	1	Using where
1	SIMPLE	sessions	eq_ref	PRIMARY	PRIMARY	3	core.profiles.user_id	1

 

is that "ALL PRIMARY 3 Using temporary; Using filesort" a bad thing?

I'm not sure why it's not using the key, other than the fact that the table has 3 rows and every row ends up eventually being selected (usually).

 

Do keys not get used if during a query, the entire table is selected? If so, why the filesort?

[fenway]

No key usage with DESC.

[neoform]

...  mysql can't just flip the results? like, seriously?

[fenway]

...  mysql can't just flip the results? like, seriously?

How fast can you recite the alphabet backwards?  It is flipping the *results*, that's why it's expensive.

[neoform]

I'm not a computer..

 

And if I'm reading from a list, I can read the alphabet backwards just as fast as read forwards...

 

I tested it, and changing DESC to ASC did nothing. That's not the issue apparently.

[fenway]

Sorry, didn't notice that you were using the expression "distance" in your order by clause... that's the "real" problem.