On SpaceBattles, I grabbed the longest thread with;
[CODE="sql"]select thread_id,reply_count from xf_thread order by reply_count desc limit 1;[/CODE]
It has 83852 replies.
Running;
[CODE="sql"]analyze
SELECT post_id, post_date, user_id, username, reaction_score, reactions
FROM xf_post
WHERE thread_id = '370782'
ORDER BY post_date
LIMIT 1;[/CODE]
Gives the output;
[CODE]+------+-------------+---------+-------+-----------------------------------------------------------------------+-----------+---------+-------+----------+-------------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+---------+-------+-----------------------------------------------------------------------+-----------+---------+-------+----------+-------------+----------+------------+-------------+
| 1 | SIMPLE | xf_post | index | thread_id_post_date,thread_id_score_date,thread_id_position_post_date | post_date | 4 | const | 45721965 | 20106107.00 | 0.35 | 0.00 | Using where |
+------+-------------+---------+-------+-----------------------------------------------------------------------+-----------+---------+-------+----------+-------------+----------+------------+-------------+
1 row in set (2 min 51.652 sec)
[/CODE]
(json format output)
[code=json]
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 172191,
"table": {
"table_name": "xf_post",
"access_type": "index",
"possible_keys": [
"thread_id_post_date",
"thread_id_score_date",
"thread_id_position_post_date"
],
"key": "post_date",
"key_length": "4",
"used_key_parts": ["post_date"],
"ref": ["const"],
"r_loops": 1,
"rows": 45721999,
"r_rows": 2.01e7,
"r_total_time_ms": 170007,
"filtered": 0.3495,
"r_filtered": 5e-6,
"attached_condition": "xf_post.thread_id <=> '370782'"
}
}
}
[/code]
And yes that is nearly 3 minutes to run the query and return the query plan.
This degenerates into what is essentially a table-scan checking some 20 million rows.
With the index hint;
[CODE]+------+-------------+---------+------+---------------------+---------------------+---------+-------+--------+--------+----------+------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+--------+--------+----------+------------+-------------+
| 1 | SIMPLE | xf_post | ref | thread_id_post_date | thread_id_post_date | 4 | const | 177448 | 1.00 | 100.00 | 100.00 | Using where |
+------+-------------+---------+------+---------------------+---------------------+---------+-------+--------+--------+----------+------------+-------------+
1 row in set (0.000 sec)
[/CODE]
[CODE="json"]{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.0492,
"table": {
"table_name": "xf_post",
"access_type": "ref",
"possible_keys": ["thread_id_post_date"],
"key": "thread_id_post_date",
"key_length": "4",
"used_key_parts": ["thread_id"],
"ref": ["const"],
"r_loops": 1,
"rows": 177448,
"r_rows": 1,
"r_total_time_ms": 0.0279,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "xf_post.thread_id <=> '370782'"
}
}
}[/CODE]
~0.05ms vs 2.8 minutes.
Likely need to patch both rebuildLastPostInfo and rebuildLastPostInfo.
:Edit:
So the crazy thing is this is hitting the longest thread on SpaceBattles (with ~41 million posts in xf_post) for a thread with 83852 replies, but not Sufficient Velocity (with ~16 million posts in xf_post) for a thread with 446620 replies.
MySQL is just reporting stupidly undercounted row estimates for one site, and not the other.
