Slow mla_gallery

  • Resolved Dima Stefantsov

    (@dima-stefantsov)


    7 years, 2 months ago

    Hi. I am using this shortcode:

    mla_gallery attachment_category="fun" size="vk-thumb" columns="0" mla_itemwidth="24%"?order="ASC"

    It produces this sql:

    SELECT wp_posts.*
FROM wp_posts 
LEFT 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 (235) )
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

    It takes 4-6 seconds to query. If I just remove ‘LEFT JOIN wp_posts AS p2 ON (p2.ID = p2.ID)’, nothing changes in results, but now it takes 0.01 sec, like it should.

    Is there some arcane meaning in joining ON (p2.ID = p2.ID) , on the same table?

    If not, could you please fix it?

    ps: temporary speeding it up with post_status=”publish”, it’s still the same, but at least now it’s 1-2 sec.

    The page I need help with: [log in to see the link]

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

    (@dglingren)

    Thanks for your question and for taking the time to dig out and post the SQL query generated by your [mla_gallery] shortcode. Your situation is similar to those described in several earlier support topics. The most recent topic is this one:

    504 Time-Out issue

    In that topic you can find links to four additional, earlier topics along the same lines. Just below the links to earlier topics is an outliine of a solution that can be adapted to solve your problem.

    Briefly, the SQL you posted is generated by WordPress, not by MLA. I developed an example plugin that generates much more efficient SQL if your application meets some simplifying conditions. You can get the details in the earlier topics.

    You can skip the “Development Version” portion of the suggested fix; the current MLA version has everything you need. Simple install and activate the “MLA Tax Query Example” plugin according to the directions, then alter your shortcode to use it. You should see much better performance once that’s done. For your example, the modification is straightforward:

    [mla_gallery my_custom_sql='attachment_category="fun"' size="vk-thumb" columns="0" mla_itemwidth="24%" order="ASC"]

    I hope the earlier topics and the above suggestions give you the results you seek. I am marking this topic resolved, but please update it if you have any problems or further questions regarding the example plugin and its use to improve your query performance. Thanks for your interest in the plugin.

    Thread Starter Dima Stefantsov

    (@dima-stefantsov)

    Thank you David!
    In my case final shortcode is [mla_gallery my_custom_sql='attachment_category="fun"?include_children="true" orderby="date" order="ASC"' size="vk-thumb" columns="0" mla_itemwidth="24%"]
    to include child att categories. Now query time almost non-existent, good.

    Is there a reason why you don’t have it by default for [mla_gallery]? ~5sec query for ~200 images is kind of insane.

    I have indeed tried to search forum before asking, but that was way too complicated, feels like immersing myself into linux kernel drivers programming ??

    Plugin Author David Lingren

    (@dglingren)

    Thanks for your update with the good news regarding the example plugin. I share your frustration about searching the support forum – this has come up time and time again with no improvements made. Have a look at this earlier tip:

    Many Warnings

    You asked “Is there a reason why you don’t have it by default for [mla_gallery]?” My policy has always been to use WordPress core API functions wherever possible. This preserves compatibility, accommodates improvements in new WordPress versions and avoids re-inventing the wheel. The issue you’ve experienced is a side effect of the flexibility and power of the taxonomy queries built in to the WP_Query class MLA uses for its [mla_gallery] queries. I doubt I could preserve all of the options offered by WP_Query with better implementation of my own.

    That said, I have noticed this specific problem in other applications. I am thinking of writing it up as a bug/feature request and submitting it to the WordPress Trac system. I will post an update here if I have any progress to report.

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