Fixed Catastrophically poor performance when viewing "IP" dialog & in approval queue

Xon

Well-known member
Affected version
2.2.8 Patch 1
While the "Delete IP usage data after" option does help, MySQL can still pick the utterly wrong indexes which can result in very bad performance.

In the IP dialog, and the approval queue, \XF\Entity\User::getIp calls XF\Repository\IP::getLoggedIp which then triggers this query;

SQL:
SELECT ip
FROM xf_ip
WHERE content_type = 'user'
        AND content_id = 'xxx'
        AND action = 'register'
ORDER BY log_date DESC
LIMIT 1;

However the indexes for the table are;
PHP:
$table->addKey(['user_id', 'log_date']);
$table->addKey(['ip', 'log_date']);
$table->addKey(['content_type', 'content_id']);
$table->addKey('log_date');

The content_type_content_id index is good enough, but log_date absolutely needs to be avoided as this will result in multiple full-table scan when viewing the approval queue.

user_id_log_date would need the user_id to be sent to getLoggedIp (or for getLoggedIp to be smart enough to set user_id when doing content_type = 'user')
 

VersoBit

Well-known member
I can confirm we experience the same slowness on this query at Fellowsfilm (and with a few of our clients who run XenForo).
 

Jeremy P

XenForo developer
Staff member
@Xon Can you confirm whether adding the user_id clause results in MySQL picking the correct index?

SQL:
SELECT ip
FROM xf_ip
WHERE content_type = 'user'
	AND content_id = 'xxx'
	AND action = 'register'
	AND user_id = 'xxx'
ORDER BY log_date DESC
LIMIT 1
 

Xon

Well-known member
Still catastrophically poor performance. I've started a conversation with additional details.

Adding the user_id isn't enough, at least with MariaDB 10.3.x (which is one of the older stable branches but still supported)
 

XF Bug Bot

XenForo bug fixer bot
Staff member
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.10).

Change log:
Pass an index hint when performing certain IP lookups
There may be a delay before changes are rolled out to the XenForo Community.
 
Top