Slow query on 2.6.3

  • Resolved Ko Takagi

    (@ko31)


    2 years, 1 month ago

    After updating to version 2.6.3, the following slow query occurred frequently and caused a very high database load on our website:

    SELECT post_mime_type, COUNT( * ) AS num_posts FROM wp_posts WHERE post_type = 'attachment' AND post_status != 'trash' AND (post_mime_type LIKE 'image/%') GROUP BY post_mime_type;

    As far as I can tell, the cause is the wp_count_attachments function that is called on every request to the site through a library.

    Although our website has a huge data over a million records, this query seems to have a significant impact on performance in this case. So, it would be happy if this could be improved.

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Support Vytis

    (@bvytis)

    Hi @ko31,

    Thank you for reporting this and I’m sorry to hear about this issue.

    Does the issue get resolved if you roll back to the previous version? I don’t see something in the recent update that could be causing such an issue.

    Looking forward to your reply. Thanks!

    Thread Starter Ko Takagi

    (@ko31)

    Hi @bvytis,

    I’m afraid I didn’t try to roll back the previous version, but I have confirmed that the slow query stopped when I deactivated this plugin. Before updating to version 2.6.3, this query did not appear.

    Also, the query seems to occur on every request with just installing this plugin, even if the status is not activated in the general settings.
    https://snipboard.io/RDeKYL.jpg

    Thank you.

    Plugin Support Vytis

    (@bvytis)

    Hi @ko31,

    Thank you for sharing more details. But from the code it doesn’t look like it could cause slow queries, I see in your screenshot that it’s also shown as 0.0002s – https://vertis.d.pr/i/hZPVzC

    Or is it a test website and on your real one it creates slowness because there is huge amount of media files? What is ~ number we’re talking about?

    Thanks!

    Thread Starter Ko Takagi

    (@ko31)

    Hi @bvytis,

    I’m sorry for confusing, but the screenshot I posted was on my test website. Our real website has over 700,000 wp_posts records and over 100,000 media files, and logs showed a slow query of 35.334482 seconds by that query.

    For reference, I tried to run the query on our website using the EXPLAIN statement as follows:

    $ wp db query "EXPLAIN SELECT post_mime_type, COUNT( * ) AS num_posts FROM wp_posts WHERE post_type = 'attachment' AND post_status != 'trash' AND (post_mime_type LIKE 'image/%') GROUP BY post_mime_type"
    +------+-------------+----------+-------+------------------+------------------+---------+------+--------+---------------------------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+----------+-------+------------------+------------------+---------+------+--------+---------------------------------------------------------------------+
    | 1 | SIMPLE | wp_posts | range | type_status_date | type_status_date | 164 | NULL | 136235 | Using index condition; Using where; Using temporary; Using filesort |
    +------+-------------+----------+-------+------------------+------------------+---------+------+--------+---------------------------------------------------------------------+

    Since post_mime_type doesn’t have an index, Using temporary; Using filesort seems to occur. It could have an impact on performance in a larger website.

    I feel like it’s a kind of problem to be ran this query on every request, even if the status isn’t activated in the general settings of this plugin. What do you think?

    • This reply was modified 2 years ago by Ko Takagi.
    Plugin Author Themeisle

    (@themeisle)

    Hi @ko31,

    Yes, you are correct, it’s a regression on our end, we will release an update with the fix in 24h.

    Thank you for reporting and sorry for the issues caused.

    • This reply was modified 2 years ago by Themeisle.
    Plugin Author Themeisle

    (@themeisle)

    Hey @ko31,

    We rolled out an update, feel free to update.

    Thank you for your patience!

    Thread Starter Ko Takagi

    (@ko31)

    Hi @themeisle,

    I have tried the update and confirmed that the query is no longer being called. Excellent!

    Thank you for your great support.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Slow query on 2.6.3’ is closed to new replies.