Slow performance - Query without an index

[Ghust]

Hey,

last weeks I've been having complaints from our users that our (self hosted) IP is slower than before. 
First I tried tweaking php-fpm settings, but I never really saw enough concurrent connections to really justify upping the max_clients to something absurd.

Then I saw that the database seemed to be having difficulties.

 

I enabled slow query logging and found the following query that was really slow (> 5seconds) for about every user. 

 

MariaDB [mysql]> select * from slow_log limit 10;
+----------------------------+--------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| start_time                 | user_host                            | query_time      | lock_time       | rows_sent | rows_examined | db    | last_insert_id | insert_id | server_id | sql_text                                                                                                                                                                                                                                                                                                                                                                                                                                      | thread_id | rows_affected |
+----------------------------+--------------------------------------+-----------------+-----------------+-----------+---------------+-------+----------------+-----------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------+
| 2024-07-28 08:38:04.975686 | forumrsca[forumrsca] @  [172.19.0.8] | 00:00:07.687649 | 00:00:00.000022 |         1 |       2595147 | forum |              0 |         0 |         1 | /*forum::forumrsca::IPS\Content\_Item::_comments:2109*/ SELECT forums_posts.*, author.*, author_pfields.* FROM `forums_posts`  LEFT JOIN `core_members` AS `author` ON author.member_id = forums_posts.author_id  LEFT JOIN `core_pfields_content` AS `author_pfields` ON author_pfields.member_id=author.member_id WHERE forums_posts.topic_id=8215 AND forums_posts.queued!=-2 AND forums_posts.queued!=-3 ORDER BY post_date ASC LIMIT 0,1 |   3542766 |


When running an EXPLAIN i see the following:

 

MariaDB [forum]> EXPLAIN SELECT forums_posts.*, author.*, author_pfields.* FROM forums_posts LEFT JOIN core_members AS author ON author.member_id = forums_posts.author_id LEFT JOIN core_pfields_content AS author_pfields ON author_pfields.member_id = author.member_id WHERE forums_posts.topic_id = 8215 AND forums_posts.queued != -2 AND forums_posts.queued != -3 ORDER BY post_date ASC LIMIT 0, 1;
+------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+
| id   | select_type | table          | type   | possible_keys                                         | key       | key_len | ref                          | rows | Extra       |
+------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+
|    1 | SIMPLE      | forums_posts   | index  | queued,topic_id,first_post,idx_topic_queued_post_date | post_date | 5       | NULL                         |  835 | Using where |
|    1 | SIMPLE      | author         | eq_ref | PRIMARY,mgroup                                        | PRIMARY   | 8       | forum.forums_posts.author_id |    1 | Using where |
|    1 | SIMPLE      | author_pfields | eq_ref | PRIMARY                                               | PRIMARY   | 3       | forum.author.member_id       |    1 | Using where |
+------+-------------+----------------+--------+-------------------------------------------------------+-----------+---------+------------------------------+------+-------------+
3 rows in set (0.51 sec)

It's using post_date here, which isn't really ideal as there's no indexing on topic_id

I then tried creating this index:

 

CREATE INDEX idx_topic_id_queued_post_date ON forums_posts(topic_id, queued, post_date);

 

But it's not being used in this query. And admin CP doesn't agree with my fix 😄 

 

 

How could I fix this issue? Is this a known one?

I guess I could drop the post_date index and recreate it with the topic_id and queued instead... But hey, I'd rather double check 🙂 

 

Added info: Invision Community v4.7.17

Edited July 28 by Ghust

[Ghust]

Addendum: I dropped the post_date index for now, it seems to be running better. 

But I'm now a bit scared that future updates will break, so if I have to recreate post_date instead, let me know!

Edited July 28 by Ghust

[Marc]

We would not advise on removing indexes, no. They will be simply readded on the next update. I will however pass this along to our developers

[Ghust]

Hey Marc, 

 

Assuming I drop my new index and recreate it as post_date, I should be fine right?

Unless something has to be changed on a specific index (such as implementing the topic_id 😄 ) I doubt an upgrade will drop and recreate all indexes, would take ages

[Marc]

Yes, that would be correct. You may find its not an issue on the next release though. Always test first