MySQL Count(*) Incorrect Value – Troubleshooting Guide

countMySQLmysql-5.7spatial

Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the count returns 3179 records. I'm sure I'm messing something up with it. Here's the two procedures:

Geo Search

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    campaign_inventory.*, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi, cpl desc
LIMIT `start`,`limit`;

END

Search Count

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    count(*) as search_results, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi desc;

END

I'm sure there's something funky. This is the first procedure I've put together and I'm not very familiar with them. But I think the issue may be in the query itself. Any input and suggestions are greatly appreciated. Let me know if there any additional info that would help.

Thanks a lot.

Best Answer

Alrighty well I sorted it out. It was indeed the having clause messing things up. Took advice from a similar issue and wrapped the count query in a query to get the count of the results. Should have dug up the solution earlier but searching for "get count in query with having clause" returns thousands of results for Having Count(*) type of queries. Thanks for the advice though guys. I appreciate it. 

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);
SELECT COUNT(*) as search_results FROM (
SELECT
    id, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
) search_counts;

END
Related Question