Bug #23528 Clarification Requested

Slow channel entries query with 15+ channel fields

Version: 4.1.1 Reporter: Andrew Gunstone

I suspect that this affect all EE4 versions … but I’ve found when adding a larger number of fields the channel entries queries run progressively slower. I currently have round 20 fairly basic fields for an “event” channel. I only have round 5 entries in the channel so far. I’m then using Low Search, but finding it incredibly slow (around 20+ seconds).

Using the debug info built into EE (yay!), I found the query was being called via Low Search:

$tagdata = $channel->entries();

This then led me to mod.channel.php … where I have changed some of the code (very bad to modify core I know!!) …

Line 2534 and line 2862 … change them both from:

$from .= "LEFT JOIN {$table} ON t.entry_id = {$table}.entry_id ";

to:

$from .= "LEFT JOIN {$table} FORCE INDEX(entry_id) ON t.entry_id = {$table}.entry_id ";

The query is now super fast. I’m not sure if it’s only because I have a low number of entries in the channel that MySQL (5.6.38) is not optimising the indexes automatically … but this change has made a dramatic difference.

Hope fully this (or a better solution if you know one!) can be rolled into core.

Cheers!

  • 20 fields and 5 entries taking 20 seconds on the data query? Something must be awry, MySQL can consider millions of records in tenths of a second. Even without indices scanning 5 rows from narrow tables 20 times over should be an eye blink for you.

    Without your modification in place, can you take the slow query and rerun it directly in MySQL with EXPLAIN in front of it?, e.g. EXPLAIN SELECT t.entry_id, t.channel_id, ... and cut and paste both the full query and the results here, please? All of those tables involved should have entry_iddefined as an index, and the JOIN is explicit; I don’t see why the query optimizer on your server would be considering anything else at all, but the EXPLAIN might, well, explain it.

    Lastly, what type of environment is this? Local dev, managed host? Is the DB server a “cloud” server like MS Azure or Rackspace Cloud Sites? Thanks!

    Derek Jones
    14th March, 2018 at 8:27pm
  • Hey Derek,

    I’m having to split this over multiple comments as I can only submit 5000 characters per comment. 😊

    The result was the same on my local MAMP Pro, and on my staging which is a pretty generic shared server with Serversaurus (https://serversaurus.com.au/).

    As you will see from the EXPLAIN results the Key is empty for all the channel_data_x joins.

    Cheers.

    Andrew Gunstone
    15th March, 2018 at 2:18am
  • 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.screen_name, 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,
          wd.*, exp_channel_data_field_4.field_id_4, exp_channel_data_field_4.field_ft_4, exp_channel_data_field_5.field_id_5, exp_channel_data_field_5.field_ft_5, exp_channel_data_field_6.field_id_6, exp_channel_data_field_6.field_ft_6, exp_channel_data_field_7.field_id_7, exp_channel_data_field_7.field_ft_7, exp_channel_data_field_8.field_id_8, exp_channel_data_field_8.field_ft_8, exp_channel_data_field_9.field_id_9, exp_channel_data_field_9.field_ft_9, exp_channel_data_field_12.field_id_12, exp_channel_data_field_12.field_ft_12, exp_channel_data_field_13.field_id_13, exp_channel_data_field_13.field_ft_13, exp_channel_data_field_14.field_id_14, exp_channel_data_field_14.field_ft_14, exp_channel_data_field_15.field_id_15, exp_channel_data_field_15.field_ft_15, exp_channel_data_field_16.field_id_16, exp_channel_data_field_16.field_ft_16, exp_channel_data_field_17.field_id_17, exp_channel_data_field_17.field_ft_17, exp_channel_data_field_18.field_id_18, exp_channel_data_field_18.field_ft_18, exp_channel_data_field_19.field_id_19, exp_channel_data_field_19.field_ft_19, exp_channel_data_field_20.field_id_20, exp_channel_data_field_20.field_ft_20, exp_channel_data_field_21.field_id_21, exp_channel_data_field_21.field_ft_21, exp_channel_data_field_22.field_id_22, exp_channel_data_field_22.field_ft_22, exp_channel_data_field_23.field_id_23, exp_channel_data_field_23.field_ft_23, exp_channel_data_field_24.field_id_24, exp_channel_data_field_24.field_ft_24, exp_channel_data_field_26.field_id_26, exp_channel_data_field_26.field_ft_26, exp_channel_data_field_27.field_id_27, exp_channel_data_field_27.field_ft_27, exp_channel_data_field_28.field_id_28, exp_channel_data_field_28.field_ft_28, exp_channel_data_field_29.field_id_29, exp_channel_data_field_29.field_ft_29, exp_channel_data_field_30.field_id_30, exp_channel_data_field_30.field_ft_30, exp_channel_data_field_32.field_id_32, exp_channel_data_field_32.field_ft_32, exp_channel_data_field_35.field_id_35, exp_channel_data_field_35.field_ft_35, exp_channel_data_field_36.field_id_36, exp_channel_data_field_36.field_ft_36, exp_channel_data_field_37.field_id_37, exp_channel_data_field_37.field_ft_37, exp_channel_data_field_38.field_id_38, exp_channel_data_field_38.field_ft_38
    Andrew Gunstone
    15th March, 2018 at 2:19am
  • 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_channel_data_field_4 ON t.entry_id = exp_channel_data_field_4.entry_id LEFT JOIN exp_channel_data_field_5 ON t.entry_id = exp_channel_data_field_5.entry_id LEFT JOIN exp_channel_data_field_6 ON t.entry_id = exp_channel_data_field_6.entry_id LEFT JOIN exp_channel_data_field_7 ON t.entry_id = exp_channel_data_field_7.entry_id LEFT JOIN exp_channel_data_field_8 ON t.entry_id = exp_channel_data_field_8.entry_id LEFT JOIN exp_channel_data_field_9 ON t.entry_id = exp_channel_data_field_9.entry_id LEFT JOIN exp_channel_data_field_12 ON t.entry_id = exp_channel_data_field_12.entry_id LEFT JOIN exp_channel_data_field_13 ON t.entry_id = exp_channel_data_field_13.entry_id LEFT JOIN exp_channel_data_field_14 ON t.entry_id = exp_channel_data_field_14.entry_id LEFT JOIN exp_channel_data_field_15 ON t.entry_id = exp_channel_data_field_15.entry_id LEFT JOIN exp_channel_data_field_16 ON t.entry_id = exp_channel_data_field_16.entry_id LEFT JOIN exp_channel_data_field_17 ON t.entry_id = exp_channel_data_field_17.entry_id LEFT JOIN exp_channel_data_field_18 ON t.entry_id = exp_channel_data_field_18.entry_id LEFT JOIN exp_channel_data_field_19 ON t.entry_id = exp_channel_data_field_19.entry_id LEFT JOIN exp_channel_data_field_20 ON t.entry_id = exp_channel_data_field_20.entry_id LEFT JOIN exp_channel_data_field_21 ON t.entry_id = exp_channel_data_field_21.entry_id LEFT JOIN exp_channel_data_field_22 ON t.entry_id = exp_channel_data_field_22.entry_id LEFT JOIN exp_channel_data_field_23 ON t.entry_id = exp_channel_data_field_23.entry_id LEFT JOIN exp_channel_data_field_24 ON t.entry_id = exp_channel_data_field_24.entry_id LEFT JOIN exp_channel_data_field_26 ON t.entry_id = exp_channel_data_field_26.entry_id LEFT JOIN exp_channel_data_field_27 ON t.entry_id = exp_channel_data_field_27.entry_id LEFT JOIN exp_channel_data_field_28 ON t.entry_id = exp_channel_data_field_28.entry_id LEFT JOIN exp_channel_data_field_29 ON t.entry_id = exp_channel_data_field_29.entry_id LEFT JOIN exp_channel_data_field_30 ON t.entry_id = exp_channel_data_field_30.entry_id LEFT JOIN exp_channel_data_field_32 ON t.entry_id = exp_channel_data_field_32.entry_id LEFT JOIN exp_channel_data_field_35 ON t.entry_id = exp_channel_data_field_35.entry_id LEFT JOIN exp_channel_data_field_36 ON t.entry_id = exp_channel_data_field_36.entry_id LEFT JOIN exp_channel_data_field_37 ON t.entry_id = exp_channel_data_field_37.entry_id LEFT JOIN exp_channel_data_field_38 ON t.entry_id = exp_channel_data_field_38.entry_id WHERE t.entry_id IN (31,29,28,27)ORDER BY FIELD(t.entry_id, 31,29,28,27)
    Andrew Gunstone
    15th March, 2018 at 2:19am
  • And the results with EXPLAIN is (comma separated results):

    "id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
    1,"SIMPLE","t","range","PRIMARY","PRIMARY",4,NULL,4,"Using where; Using temporary; Using filesort"
    1,"SIMPLE","w","eq_ref","PRIMARY","PRIMARY",4,"db_whw.t.channel_id",1,NULL
    1,"SIMPLE","wd","eq_ref","PRIMARY","PRIMARY",4,"db_whw.t.entry_id",1,NULL
    1,"SIMPLE","m","ALL","PRIMARY",NULL,NULL,NULL,2,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_4","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_5","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_6","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_7","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_8","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_9","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_12","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_13","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_14","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_15","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_16","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_17","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_18","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_19","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_20","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_21","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_22","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_23","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_24","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_26","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_27","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_28","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_29","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_30","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_32","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_35","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_36","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_37","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    1,"SIMPLE","exp_channel_data_field_38","ALL","entry_id",NULL,NULL,NULL,5,"Using where; Using join buffer (Block Nested Loop)"
    Andrew Gunstone
    15th March, 2018 at 2:20am
  • That’s very strange, I’d almost expect that if none of the fields had any content, but that should in fact be very speedy. It’s showing correctly that it’s only scanning 5 rows for the join. When you run this query on its own directly in MySQL (or whatever utility you use in MAMP), does it take 20 seconds?

    In general forcing index is a bad move as it can prevent the optimizer from making the best choices, so I doubt that’s an accommodation we would add in. I’d like to figure out what’s up in your environments though. We’ve not had anyone else report this type of issue, and in fact our schema performance audit we conducted with Percona did not experience this either, at any level of scale.

    Would it be possible to get a copy of your database, as well as the full output from SHOW VARIABLES;?

    Derek Jones
    15th March, 2018 at 12:54pm

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

.(JavaScript must be enabled to view this email address)

ExpressionEngine News!

#eecms, #events, #releases