Slow queries

  • Resolved aptharsia

    (@aptharsia)


    10 years, 5 months ago

    I’m getting very slow queries when I use my tag gallery. I used Query Monitor to show me what’s going on, as to why it’s taking so long to load the tag page and this is what it’s showing me:

    SELECT wp_posts.ID
FROM wp_posts
INNER JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_posts AS p2
ON (p2.ID = p2.ID)
WHERE 1=1
AND ( wp_term_relationships.term_taxonomy_id IN (713) )
AND (wp_posts.post_mime_type LIKE 'image/%')
AND wp_posts.post_type = 'attachment'
AND (((wp_posts.post_status = 'inherit')
OR (wp_posts.post_status = 'inherit'
AND (p2.post_status = 'inherit'))))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC, wp_posts.ID ASC
LIMIT 0, 30

    Caller: WP_Query->get_posts()+
    Component: Plugin: media-library-assistant
    Affected Rows: 30
    Time: 26.9940

    I have no clue what it all means, but hoping someone else had the same problem and found a solution or someone can tell me why it’s doing it and how to fix it. Appreciate it.

    https://www.ads-software.com/plugins/media-library-assistant/

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author David Lingren

    (@dglingren)

    Thank you for working with me by e-mail to investigate this issue and to resolve it. The query you posted above is generated by the WordPress get_posts() function when a “simple taxonomy” is given as a parameter, e.g., attachment_tag=fireplaces. As you can see, it is fairly complex.

    In your specific case, the “attachment_tag” taxonomy is only used for image attachments, so a much simpler query is possible:

    SELECT DISTINCT tr.object_id
FROM wp_term_relationships as tr
WHERE ( tr.term_taxonomy_id IN ('713') )

    Replacing the WordPress “simple taxonomy” query with the simpler SQL query was accomplished with a small custom plugin that hooked three of the filters provided by the [mla_gallery] shortcode. The plugin also handles MLA pagination (mla_output="paginate_links,prev_next").

    I will include a version of the plugin as mla-tax-query-example.php.txt in the /media-libarary-assistant/examples/ directory for the next MLA release.

    I am marking this topic resolved. Thanks again for working with me on an example that may help other MLA users.

    Hello David,

    I’ve come up against a similar problem lately. Not only are my mla galleries slow loading due to the lengthy queries, but those same queries are timing out the mla assistant in my WP dashboard.

    Thank you for addressing this issue and creating a plugin to help with this.
    I tried adding your tax query example as a plugin and activated it, but there was no effect, unfortunately. Am I supposed to use that file “as is” or do I need to change anything inside of it? I’m not a programmer so I get lost on the tech stuff ??

    Thank you!
    Michele

    Plugin Author David Lingren

    (@dglingren)

    Michele,

    Good to hear from you. I’m happy you found the tax query example and you’re close to a solution.

    In addition to installing and activating the plugin you must make some changes in the [mla_gallery] shortcodes you want to use it. The custom plugin looks for a “my_custom_sql” parameter and only takes charge when it finds one.

    If you can post the source text for the shortcode(s) you are using I can give you more specific guidance on the changes required. You can post the here or forward them by e-mail; I believe you still have my contact information.

    I look forward to getting you the performance improvements you need.

    Hi David,

    Thank you so much for your reply. I was able to get the “my_custom_sql” shortcode worked out for the plug-in to kick in, and on the page where I’ve added it, the load time is dramatically reduced. Yippee!

    Since I have over 500 attachment categories, my next step will be to perform a search and replace in the database to modify the shortcodes for all of the galleries. Fingers crossed.

    Could you take a quick look and tell me if what I’ve worked out looks right?

    Old shortcode:
    [mla_gallery mla_alt_shortcode=gallery royalslider="3" orderby="title" order="ASC" attachment_category="alchemical-alice"]

    New shortcode:

    [mla_gallery mla_alt_shortcode=gallery royalslider="3" orderby="title" order="ASC" my_custom_sql="attachment_category=alchemical-alice"]

    Search:
    attachment_category=”

    Replace:
    my_custom_sql=”attachment_category=

    Again, thanks so much for your efforts with this plugin. It’s in my top 3 most useful plugins ever.

    Warm regards,
    Michele

    Plugin Author David Lingren

    (@dglingren)

    Thank you for your update and for the great news. I am very happy that the example plugin is working for you and the results are positive.

    Your Search and Replace strings look good, assuming you don’t use attachment_category=" in some other context on your site. Of course, you should definitely make a database backup before running the update! You could also do a “Find All” before the search and replace and have a look at the results, just to confirm the scope of your updates.

    Thanks for your kind words and for your interest in the plugin. Positive feedback and good reviews are a big motivator to keep working on the plugin and supporting its users.

    Search and replace was successful, galleries are loading fast, and MLA is accessible again through my dashboard – I’m super happy! (If only I could give MLA 10 stars.)

    Thank you David!

    Cheers, Michele

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Slow queries’ is closed to new replies.