Bug #23194 Accepted

member_data tables still being queried when disabled in channel entries tag

Version: 3.5.10 Reporter: James Catt

I have a channel:entries tag pair that’s pulling a list of all blog entries in the system. I keep getting a blank page when trying to spit them out all at once, presumably because the DB query identified below is too much of a memory hog (the template works as long as I keep the entry limit low, but beyond about 100 it starts failing).

I’ve tried setting disable="member_data" to improve the performance, but I noticed that the query is still calling two LEFT JOINs on the member-related tables––shouldn’t this be eliminated from the query when setting the disable?

Here’s the channel entries call:

{exp:channel:entries channel="blog" limit="999" status="open" disable="member_data"}

…and here’s the query I’m seeing in the debugger, which has a little exclamation mark next to it. Note that I’ve truncated the full list of IDs for brevity

SELECT  t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id,
         w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled,
         m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio,
       FROM exp_channel_titles  AS t
       LEFT JOIN exp_channels   AS w  ON t.channel_id = w.channel_id
       LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id
       LEFT JOIN exp_members  AS m  ON m.member_id = t.author_id
       LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id WHERE t.entry_id IN (12926,12914,12903, [** full ID list removed for brevity **]) ORDER BY t.sticky desc, t.entry_date desc, t.entry_id desc

…shouldn’t those last two LEFT JOINs be excluded in this scenario?

  • The exp_members JOIN is expected, since that’s required to have basic information about the entry author (name, group ID, permissions, etc). But the data table should not be joined, nor its columns selected. This parameter currently is just avoiding an additional query to discover the names of the custom member fields so this is definitely a bug, and appears to be the case going all the way back to v1!

    What’s the rough size of your exp_members and exp_member_data tables, if I may ask? Exceeding available memory with just over 100 entries is rather alarming, and does not sound like this bug is the only issue you are having.

    Derek Jones
    04th August, 2017 at 3:27pm
  • Both tables only have 22 rows. I doubt that it’s the member_data that’s blowing out the memory, I just came across this as I was trying to whittle it down as much as possible.

    To be clear, I can’t be certain that it’s a memory issue, that’s just my best guess. The observed behaviour is that the server responds with a “200 OK” response code but no response body (hence the blank white page) after chewing on it for about 10 seconds. I tried increasing the PHP memory limit from 256 to 1024 but it made no difference. I haven’t messed with the MySQL memory settings. I’m not getting any errors displayed, and there’s nothing in the Apache log.

    When running the template on our production server (which has more hardware horsepower) I’m able to push it to a limit of 200 entries before it gives out on me.

    James Catt
    04th August, 2017 at 3:39pm
  • Ah, if you were running out of memory, PHP would have a fatal crash, and would not issue a 200 response code. I would first try disabling all extensions to see if one of them is running away with itself. If that doesn’t help you identify the issue, feel free to put in a support ticket and we’d be happy to help you diagnose it. In any case, thanks for finding and reporting this bug!

    Derek Jones
    04th August, 2017 at 3:42pm

You must be signed in to comment on a bug report.

ExpressionEngine News

#eecms, #events, #releases