Fixed \XF\Repository\Ip::getIpsByUser doesn't have an appropriate index

PaulB

Well-known member
Affected version
2.2.9
getIpsByUser effectively uses the following query:
SQL:
SELECT ip,
    MIN(log_date) AS first_date,
    MAX(log_date) AS last_date,
    COUNT(*) AS total
FROM xf_ip
WHERE user_id = ?
GROUP BY ip
ORDER BY last_date DESC
LIMIT 150;
Although it looks complicated, this can be optimized quite a bit by an index on (user_id, ip, log_date DESC). It's not a perfect optimization, but it should be good enough for all but the largest forums. Larger forums may be able to get better performance by removing COUNT(*) AS total, but I haven't tested this.
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.13).

Change log:
Improve user IP lookup query performance
There may be a delay before changes are rolled out to the XenForo Community.
 
Top Bottom