Slow query - ideas for an optimization?

[Deleted member 232995]

What do I want to achieve with the query?

I would like to count the reactions that a user has received. And only in a certain forum - in which - this is decided by the PostId respectively the ThreadId.

Unfortunately, the query needs about 5 seconds for each query. This is a mammoth task with the mass of posts approx. 9 million, topics approx. 500k and users approx. 200k.

Is there a way to collect the data faster here? do I have a thinking error in the query?

SELECT COUNT(*)
FROM xf_reaction_content as rc
LEFT JOIN xf_post as p ON(rc.content_id = p.post_id AND rc.content_type = 'post')
LEFT JOIN xf_thread as t USING(thread_id)
WHERE
t.thread_id != 9
AND t.node_id = 2
AND rc.reaction_date <= 1625617542
AND rc.content_user_id = 2
AND reaction_id = 2

 

[VersoBit]

It might be better to calculate this as a cron and store the result in its own column, I don't know enough about SQL to give any advice on it, but if the query is that large, I'd definitely wouldn't want it happening on the fly.

 

[briansol]

try putting

AND rc.content_type = 'post'

into the where clause instead.

it's fast to join on ID's, but not strings.

the cron advice above is solid though. pre-cache it!

 

[Kirby]

Hmm, a few secods execution time seems to be to high to me - running such queries on a DB with approx 12M posts, 150K threads, 65K users does take < 100ms in my test.

Can you post the execution plan?
Which MySQL / MariaDB version?

 

[Deleted member 232995]

Which MySQL / MariaDB version?

MySQL 8.0.26

Can you post the execution plan?

Database changed
08:54:10 XXXX [db-1]> SELECT COUNT(*) FROM xf_reaction_content as rc LEFT JOIN xf_post as p ON(rc.content_id = p.post_id AND rc.content_type = 'post') LEFT JOIN xf_thread as t USING(thread_id) WHERE t.thread_id != 590604 AND t.node_id = 83 AND rc.reaction_date <= 1579073773 AND rc.content_user_id = 78484 AND reaction_id = 8;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (7.40 sec)

08:54:28 XXX [db-1]> EXPLAIN SELECT COUNT(*) FROM xf_reaction_content as rc LEFT JOIN xf_post as p ON(rc.content_id = p.post_id AND rc.content_type = 'post') LEFT JOIN xf_thread as t USING(thread_id) WHERE t.thread_id != 590604 AND t.node_id = 83 AND rc.reaction_date <= 1579073773 AND rc.content_user_id = 78484 AND reaction_id = 8;
+----+-------------+-------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys                                                                                                                                          | key                     | key_len | ref                  | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY,node_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_score,node_id_last_threadmark_date_last_post_date              | node_id_last_post_date  | 4       | const                | 1045 |    59.57 | Using where; Using index           |
|  1 | SIMPLE      | p     | NULL       | ref  | PRIMARY,thread_id_post_date,thread_id_position,thread_id_score_date                                                                                    | thread_id_post_date     | 4       | db-1.t.thread_id     |   15 |   100.00 | Using where; Using index           |
|  1 | SIMPLE      | rc    | NULL       | ref  | content_type_id_user_id,content_type_id_reaction_date,content_user_id_reaction_date,reaction_date,content_user_id_is_counted_reaction_id_reaction_date | content_type_id_user_id | 31      | const,db-1.p.post_id |    2 |     1.74 | Using index condition; Using where |
+----+-------------+-------+------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+------+----------+------------------------------------+
3 rows in set, 1 warning (0.00 sec)

 

[Kirby]

Hmm, I am getting a different plan on MariaDB.

Can you try

SELECT COUNT(*)
FROM xf_reaction_content as rc
LEFT JOIN xf_post  as p FORCE INDEX(PRIMARY) ON (rc.content_id = p.post_id AND rc.content_type = 'post')
LEFT JOIN xf_thread as t FORCE INDEX(PRIMARY) USING(thread_id)
WHERE t.thread_id != 590604 AND t.node_id = 83 AND rc.reaction_date <= 1579073773 AND rc.content_user_id = 78484 AND reaction_id = 8;

?

 

[Deleted member 232995]

09:30:48 XXXX [db-1]> SELECT COUNT(*)
    -> FROM xf_reaction_content as rc
    -> LEFT JOIN xf_post  as p FORCE INDEX(PRIMARY) ON (rc.content_id = p.post_id AND rc.content_type = 'post')
    -> LEFT JOIN xf_thread as t FORCE INDEX(PRIMARY) USING(thread_id)
    -> WHERE t.thread_id != 590604 AND t.node_id = 83 AND rc.reaction_date <= 1579073773 AND rc.content_user_id = 78484 AND reaction_id = 8;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.14 sec)

I did not think of it
Looks very good already - the time.