Bugfix
Member
Hello,
analyzing slow queries we found a weird use of "_getDb()->quote" in:
XenForo_Model_User -> getUsersByIds
This code is used:
There are 2 Problems:
1) Assuming this array:
you get this SQL:
So, only the first userid is quoted which doesn't make sense.
2) Not using index when quoting an integer:
Because of the quote, the primary index "xf_user.user_id" isn't used:
With 1st id quoted:
Without quoting:
Performance difference:
Notable performance differences if many user id’s used (e.g. ~1.000 user id’s):
With 1st quoted:
958 rows in set (4.88 sec)
Without quotes:
958 rows in set (0.01 sec)
All statements performed with optimizer hint ‘SQL_NO_CACHE’ in order to disable mysql statement cache.
My workaround now was using this code:
Would be great to get feedback since I absolutely want to avoid changing XF-code.
regards,
Harald
analyzing slow queries we found a weird use of "_getDb()->quote" in:
XenForo_Model_User -> getUsersByIds
This code is used:
Code:
WHERE user.user_id IN (' . $this->_getDb()->quote($userIds) . ')
1) Assuming this array:
Code:
Array
(
[0] => 139226
[1] => 114548
[2] => 123568
[3] => 126662
)
Code:
WHERE user.user_id IN ('139226', 114548, 123568, 126662)
2) Not using index when quoting an integer:
Because of the quote, the primary index "xf_user.user_id" isn't used:
With 1st id quoted:
Code:
mysql> EXPLAIN SELECT xf_user.user_id FROM xf_user WHERE xf_user.user_id IN ('139226', 114548, 123568, 126662);
+----+-------------+---------+-------+---------------+------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+------+--------+--------------------------+
| 1 | SIMPLE | xf_user | index | PRIMARY | user_state | 1 | NULL | 356315 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
Without quoting:
Code:
mysql> EXPLAIN SELECT xf_user.user_id FROM xf_user WHERE xf_user.user_id IN (139226, 114548, 123568, 126662);
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | xf_user | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Performance difference:
- With 1st id quoted:
Code:
mysql> SELECT SQL_NO_CACHE xf_user.user_id FROM xf_user WHERE xf_user.user_id IN ('139226', 114548, 123568, 126662);
+---------+
| user_id |
+---------+
| 114548 |
| 123568 |
| 126662 |
| 139226 |
+---------+
4 rows in set (0.36 sec)
- Without quoting:
Code:
mysql> SELECT SQL_NO_CACHE xf_user.user_id FROM xf_user WHERE xf_user.user_id IN (139226, 114548, 123568, 126662);
+---------+
| user_id |
+---------+
| 114548 |
| 123568 |
| 126662 |
| 139226 |
+---------+
4 rows in set (0.00 sec)
Notable performance differences if many user id’s used (e.g. ~1.000 user id’s):
With 1st quoted:
958 rows in set (4.88 sec)
Without quotes:
958 rows in set (0.01 sec)
All statements performed with optimizer hint ‘SQL_NO_CACHE’ in order to disable mysql statement cache.
My workaround now was using this code:
Code:
WHERE user.user_id IN (' . implode(",", $userIds) . ')
regards,
Harald