Bug No Answer Slow Query / Inefficient Cron-Job

[Kirby]

We are getting this slow query once every hour

# Query_time: 0.215240  Lock_time: 0.000027  Rows_sent: 100  Rows_examined: 98596
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
use xxx;
SET timestamp=1497114070;
SELECT user_profile.user_id, user_profile.location, user_ip.ip
            FROM `xf_user_profile` as user_profile
            LEFT JOIN (SELECT DISTINCT user_id, ip FROM `xf_ip` GROUP BY user_id ORDER BY log_date DESC) AS user_ip ON (user_profile.user_id =  user_ip.user_id)
            WHERE user_profile.user_id NOT IN (SELECT user_id FROM xfa_map_location) AND user_ip.ip IS NOT NULL
            ORDER BY RAND()
            LIMIT 100;

This query is caused by XFA_MembersMap_Model_Location::getAllUserProfileWithEmptyLocation() and is a total disaster

We do not use the GeoIP feature, so cheking the IP seems completely useless.

A better query would be

SELECT user_id, location
FROM `xf_user_profile`
LEFT JOIN WHERE user_id NOT IN (SELECT user_id FROM xfa_map_location)
    AND location != ''
LIMIT 100

But IMHO the best option would be to not use a cron-job at all if GeoIP is not enabled:
Many (most?) forums won't have a lot of location changes per day, so querying all users every hour (indexes can't be used) really doesn't make sense at all.
In this case it would be a lot better to update all necessary tables just in time when a user changes location or the map display setting.

Furthermore, this does process data for all users -not only for those who have xfa_map_allow_position_map = 1 which seems like a massive waste of resources - why hould we ge-code data (and use pi calls for this) for users wo not not want to be displayed?

Last but not least users with "invalid" location like "Nearby Paris" might get processed over and over again unless the setting Delete location of user is enabled (which we do not want to enable, "Nearby Paris" is perfectly fine); this should not happen.

Maybe you could use a timestamped geocode cache (which also caches nagative lookups)?
This way you could also save some API calls if several users have the same location (for example just "Paris").

 

[Fred]

We are getting this slow query once every hour

# Query_time: 0.215240  Lock_time: 0.000027  Rows_sent: 100  Rows_examined: 98596
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
use xxx;
SET timestamp=1497114070;
SELECT user_profile.user_id, user_profile.location, user_ip.ip
            FROM `xf_user_profile` as user_profile
            LEFT JOIN (SELECT DISTINCT user_id, ip FROM `xf_ip` GROUP BY user_id ORDER BY log_date DESC) AS user_ip ON (user_profile.user_id =  user_ip.user_id)
            WHERE user_profile.user_id NOT IN (SELECT user_id FROM xfa_map_location) AND user_ip.ip IS NOT NULL
            ORDER BY RAND()
            LIMIT 100;

This query is caused by XFA_MembersMap_Model_Location::getAllUserProfileWithEmptyLocation() and is a total disaster

We do not use the GeoIP feature, so cheking the IP seems completely useless.

A better query would be

SELECT user_id, location
FROM `xf_user_profile`
LEFT JOIN WHERE user_id NOT IN (SELECT user_id FROM xfa_map_location)
    AND location != ''
LIMIT 100

But IMHO the best option would be to not use a cron-job at all if GeoIP is not enabled:
Many (most?) forums won't have a lot of location changes per day, so querying all users every hour (indexes can't be used) really doesn't make sense at all.
In this case it would be a lot better to update all necessary tables just in time when a user changes location or the map display setting.

Furthermore, this does process data for all users -not only for those who have xfa_map_allow_position_map = 1 which seems like a massive waste of resources - why hould we ge-code data (and use pi calls for this) for users wo not not want to be displayed?

Last but not least users with "invalid" location like "Nearby Paris" might get processed over and over again unless the setting Delete location of user is enabled (which we do not want to enable, "Nearby Paris" is perfectly fine); this should not happen.

Maybe you could use a timestamped geocode cache (which also caches nagative lookups)?
This way you could also save some API calls if several users have the same location (for example just "Paris").

If you need you can disable cron task, I can't do much for this
Idem for location cache, geocomplete is based from google API and we haven't any control on this one so, we need do like this