504 Time-Out issue

Thanks for your patience while I was on the road. I’m back and getting caught up and ready to give your application some attention. Your suspicion that some tuning or direct SQL would improve things is correct. By default, [mla_gallery] relies on the WordPress WP_Query class for database access. For some queries, such as those that involve taxonomies, a few simplifying assumptions can make a difference.

For comparison, here is the SQL query generated by your shortcode parameters:

SELECT SQL_CALC_FOUND_ROWS mladev_posts.ID
FROM mladev_posts 
LEFT JOIN mladev_term_relationships
ON (mladev_posts.ID = mladev_term_relationships.object_id)
LEFT JOIN mladev_posts AS p2
ON (p2.ID = p2.ID) 
WHERE 1=1 
AND ( mladev_term_relationships.term_taxonomy_id IN (167,184) 
AND mladev_posts.ID NOT IN ( 
SELECT object_id 
FROM mladev_term_relationships 
WHERE term_taxonomy_id IN (26) ) )
AND (mladev_posts.post_mime_type LIKE 'image/%') 
AND mladev_posts.post_type = 'attachment'
AND (((mladev_posts.post_status = 'inherit')
OR (mladev_posts.post_status = 'inherit'
AND (p2.post_status = 'inherit'))))
GROUP BY mladev_posts.ID
ORDER BY RAND()
LIMIT 0, 99

Note the tests for mladev_posts.post_status = 'inherit' and mladev_posts.post_mime_type LIKE 'image/%'. If your application uses the Att. Category taxonomy exclusively for image attachments these tests are not required. In other words, can we assume that the only items assigned to “cat1” and “cat2” are image attachments? If so, a simpler query is possible.

Let me know if those assumptions are valid and I will work something up for your testing.

I have completed an enhancement to one of the MLA example plugins that should meet your requirements. You wrote “I also read something about direct SQL requests, but can’t find it anymore.” You may have found one of these earlier topics:

Gallery page with many images takes too long to load

MLATaxQuery with keyword search and pagination

REALLY Slow Queries…….. Help! 🙂

Slow queries

As you can see, taxonomy queries have been a performance issue for some time. These earlier topics inspired the “MLA Tax Query Example” plugin, which takes advantage of the simplifying assumption that the attachment_category taxonomy is used exclusively to assigning terms to Media Library items. If you install and activate the example plugin you can change your shortcode to something like:

[mla_gallery posts_per_page=99]
my_custom_sql="attachment_category='cat1,/cat2' include_children=true orderby=rand"
post_mime_type=all
[/mla_gallery]

In the above example:

1. The my_custom_sql parameter activates the example plugin and contains the special sub-parameters it uses for its part of the job.
2. attachment_category='cat1,/cat2' replaces the tax_query in your original shortcode.
3. The ‘cat1,/cat2’ values are the terms you want to filter on. You can have as many terms as you need.
4. The slash preceding cat2 assigns it to the “NOT IN” part of the query.
5. The include_children=true parameter matches the default used by the original tax_query. You can omit it if your terms do not have any lower-level children you want to add to the list.
6. orderby=rand must be moved inside the my_custom_sql parameter because it affects the queries required.
7. post_mime_type=all removes the test that returns only image types. If you know that only images are assigned to your terms, leave it in. If you get non-image items in your results, remove this parameter to filter them out.

I have uploaded a new MLA Development Version dated 20171029 that contains the enhanced “MLA Tax Query Example” plugin. It would be great if you can install the Development Version and example plugin and do some testing of your own.

To get the Development Version, click this link to download the Development Version ZIP archive:

https://downloads.wordpress.org/plugin/media-library-assistant.zip

Once you have the ZIP archive on your system:

1. Login to your site’s Admin area and navigate to Plugins/Installed Plugins.
   Find Media Library Assistant and deactivate it.
2. Find Media Library Assistant and delete it. You will not lose any settings.
3. Go to Plugins/Add New.
4. Click “Upload Plugin”, to the right of the Add Plugins title.<br>”Browse…” to the location of the ZIP Archive and click on it.
5. Click “Install Now”, to the right of “Browse…”
6. When the install completes, click “Activate Plugin” at the bottom of the screen.
7. When the activation completes, go back to the Plugins/Installed Plugins screen.
8. Scroll down to “Media Library Assistant” and look for a date like “20171029” at the start of the Description. That’s how you know you have a Development Version. You can also go to the Settings/Media Library Assistant submenu and see the date stamp in the heading.

That’s it. I don’t change the version number of the Development Version, so you will be notified when the next official version comes out and the normal update process will continue to work.

To install the example plugin, navigate to the Settings/Media library Assistant Documentation tab and click the “Example Plugins” button. You will see a table that lists all the example plugins and gives you a “one-click” action for installing them. Type “tax query” in the text box (including the quotes) and click “Search Plugins” to filter the table. You are looking for “MLA Tax Query Example” example plugin. Find that plugin and hover over the title in the left-most column. Click the “Install” rollover action, then go to the WordPress Plugins/Installed Plugins submenu and activate it as you would any other plugin.

Let me know if you have any problems with the above suggestions.

Thanks for the kind words and for the happy news confirming the improvements. As you can see, taxonomy queries can often benefit from the special aspects of each application.

I am leaving this topic resolved, but please update it if you have any other problems or further questions about taxonomy issues in your application.