CODEWITHSUNDEEP
mysqlhaving
Kalzem
Kalzem

Reputation: 7492

HAVING not working on all version of MySQL

I have this query :

SELECT `j`.`id`, ... (other columns), (6371 * acos( cos( radians( 22.282460 ) ) * cos( radians( j.lat ) ) * cos( radians( j.lng ) - radians( 114.16157 ) ) + sin( radians( 22.282460 ) ) * sin( radians( j.lat ) ) )
) AS `distance`
FROM `job` AS `j`
LEFT JOIN `jobs_tags` AS `jt` ON `jt`.`job_id` = `j`.`id`
LEFT JOIN `tag` AS `t` ON `t`.`id` = `jt`.`tag_id`
LEFT JOIN `user_company` AS `c` ON `c`.`user_id` = `j`.`user_id`
GROUP BY `j`.`id`
HAVING `distance` < 100
ORDER BY `j`.`creation_date` DESC , `distance` ASC
LIMIT 15

On my MySQL MAMP (Server version: 5.5.34), it returns what I want : All the jobs around Hong Kong (the current lat & lng represents Hong Kong here) within 100km.

However, on the MySQL of the server (Server version: 5.6.16), the SAME query returns cities with a distance of over 9000 (like Paris with a distance of 9629.165355675643). How come it ignores HAVING ?

Upvotes: 0

Views: 522

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

I don't understand why you would be getting different results between the servers. However, you should not be using HAVING in this case. You would use HAVING if you were applying a condition to the results of one of the aggregate functions.

The HAVING condition is applied after the GROUP BY, so MySQL is having to group all jobs, not just ones that are within 100km. You'll get better performance with the following:

SELECT `j`.`id`, ... (other columns), (6371 * acos( cos( radians( 22.282460 ) ) * cos( radians( j.lat ) ) * cos( radians( j.lng ) - radians( 114.16157 ) ) + sin( radians( 22.282460 ) ) * sin( radians( j.lat ) ) )
) AS `distance`
FROM `job` AS `j`
LEFT JOIN `jobs_tags` AS `jt` ON `jt`.`job_id` = `j`.`id`
LEFT JOIN `tag` AS `t` ON `t`.`id` = `jt`.`tag_id`
LEFT JOIN `user_company` AS `c` ON `c`.`user_id` = `j`.`user_id`
WHERE (6371 * acos( cos( radians( 22.282460 ) ) * cos( radians( j.lat ) ) * cos( radians( j.lng ) - radians( 114.16157 ) ) + sin( radians( 22.282460 ) ) * sin( radians( j.lat ) ) )
) < 100
GROUP BY `j`.`id`
ORDER BY `j`.`creation_date` DESC , `distance` ASC
LIMIT 15

With the above query, MySQL will skip over jobs that aren't within 100km, which avoids additional work having to join on, group the results, and perform aggregate functions against jobs that won't be in the final result set anyway.

If you don't have any aggregate functions, then there will be no performance improvement (they will be treated equally), but then you shouldn't be using HAVING anyway, since it's to be used with aggregate functions, not to save you from having to retype expressions.

Upvotes: 2

Related Questions