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

[PaulB]

Affected version

2.2.9

getIpsByUser effectively uses the following query:

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.

 

[XF Bug Bot]

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.