Slow SQL query in control panel > Entries > Edit

Thanks for post this.

What page / what action are you doing when this query triggers? (Looking for a best way to re-create it)

Hey there,

so basically this is happening on any channel edit page: /cp/publish/edit/entry/583906. It doesn’t seem to matter if the channel has a handful of entries or thousands.

The issue has been seen on two separate machines, one Mac one Windows so I doubt it’s an environment issue.

Here’s the full error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes) in /Users/aa/Sites/ExpressionEngine6.0.3/system/ee/legacy/database/drivers/mysqli/mysqli_result.php on line 165

The control panel in general seems to be running a touch slow, for example loading the listing screen for a channel with 1 entry takes over 2 seconds. Not sure if that is normal:

/cp/publish/edit 2.1805s Load Variables 78 Queries Performance Memory Usage: 23.4MB of 128M Database Execution Time: 1.6557 Total Execution Time: 2.1805

Attached is a screenshot of the addons being used. Not sure if any of those might be clashing. VMG & Mo variables have been ported to EE6 for us by a developer as we use those a lot.

So loading the control panel list page for one of our channels with 500,000+ entries takes over 12 seconds:

/cp/publish/edit 12.1170s Load Variables 77 Queries Performance Memory Usage: 25.9MB of 128M Database Execution Time: 11.3535 Total Execution Time: 12.1170

SQL output from that page, there appears to be a lot of queries run multiple times:

3 × SHOW COLUMNS FROM exp_member_data system/ee/ExpressionEngine/Service/Model/MetaDataReader.php L:162 ExpressionEngine\Model\Content\VariableColumnGateway::getFieldList()

2 × SELECT exp_members.member_id as Member__member_id, Member_field_id_27.m_field_id_27 as Member__m_field_id_27, Member_field_id_27.m_field_ft_27 as Member__m_field_ft_27, Member_field_id_28.m_field_id_28 as Member__m_field_id_28, Member_field_id_28.m_field_ft_28 as Member__m_field_ft_28 FROM (exp_members) LEFT JOIN exp_member_data_field_27 AS Member_field_id_27 ON Member_field_id_27.member_id = exp_members.member_id LEFT JOIN exp_member_data_field_28 AS Member_field_id_28 ON Member_field_id_28.member_id = exp_members.member_id WHERE exp_members.member_id IN (1) system/ee/legacy/libraries/Session.php L:574 EE_Session::_do_member_query() system/ee/legacy/libraries/Core.php L:449 Cp::set_default_view_variables()

3 ×

SELECT Permission_permissions.permission_id as Permission__permission_id, Permission_permissions.role_id as Permission__role_id, Permission_permissions.site_id as Permission__site_id, Permission_permissions.permission as Permission__permission FROM (exp_permissions as Permission_permissions) WHERE ( Permission_permissions.site_id = 1 AND Permission_permissions.role_id IN (1) ) LIMIT 18446744073709551615 system/ee/legacy/libraries/Session.php L:607 ExpressionEngine\Model\Member\Member::getPermissions() system/ee/legacy/libraries/Session.php L:648 ExpressionEngine\Model\Member\Member::getPermissions() system/ee/ExpressionEngine/app.setup.php L:249 ExpressionEngine\Model\Member\Member::getPermissions()

7 ×

SELECT EntryManagerView_entry_manager_views.view_id as EntryManagerView__view_id, EntryManagerView_entry_manager_views.member_id as EntryManagerView__member_id, EntryManagerView_entry_manager_views.channel_id as EntryManagerView__channel_id, EntryManagerView_entry_manager_views.name as EntryManagerView__name, EntryManagerView_entry_manager_views.columns as EntryManagerView__columns FROM (exp_entry_manager_views as EntryManagerView_entry_manager_views) WHERE ( EntryManagerView_entry_manager_views.member_id = 1 AND EntryManagerView_entry_manager_views.channel_id = 7 ) LIMIT 1 system/ee/ExpressionEngine/Service/Filter/FilterFactory.php L:275 ExpressionEngine\Service\Filter\Columns::value() system/ee/ExpressionEngine/Service/Filter/Columns.php L:73 ExpressionEngine\Service\Filter\Columns::value() system/ee/ExpressionEngine/Service/Filter/FilterFactory.php L:231 ExpressionEngine\Service\Filter\Columns::value()

2 ×

SHOW COLUMNS FROM exp_channel_data system/ee/ExpressionEngine/Service/Model/MetaDataReader.php L:162 ExpressionEngine\Model\Content\VariableColumnGateway::getFieldList()

2 × SELECT Status_statuses.status_id as Status__status_id, Status_statuses.status as Status__status, Status_statuses.status_order as Status__status_order, Status_statuses.highlight as Status__highlight FROM (exp_statuses as Status_statuses) LIMIT 18446744073709551615 L: ExpressionEngine\Controller\Publish\Edit::index() system/ee/ExpressionEngine/Library/CP/EntryManager/Columns/Status.php L:36 ExpressionEngine\Library\CP\EntryManager\Columns\Status::getStatuses()

2 × SELECT MenuItem_menu_items.item_id as MenuItem__item_id, MenuItem_menu_items.parent_id as MenuItem__parent_id, MenuItem_menu_items.set_id as MenuItem__set_id, MenuItem_menu_items.name as MenuItem__name, MenuItem_menu_items.data as MenuItem__data, MenuItem_menu_items.type as MenuItem__type, MenuItem_menu_items.sort as MenuItem__sort, Set_menu_sets.set_id as Set__set_id, RoleSettings_role_settings.id as RoleSettings__id, RoleSettings_role_settings.role_id as RoleSettings__role_id, RoleSettings_role_settings.site_id as RoleSettings__site_id, RoleSettings_role_settings.menu_set_id as RoleSettings__menu_set_id, Children_menu_items.item_id as Children__item_id, Children_menu_items.parent_id as Children__parent_id, Children_menu_items.set_id as Children__set_id, Children_menu_items.name as Children__name, Children_menu_items.data as Children__data, Children_menu_items.type as Children__type, Children_menu_items.sort as Children__sort FROM (exp_menu_items as MenuItem_menu_items) LEFT JOIN exp_menu_sets AS Set_menu_sets ON Set_menu_sets.set_id = MenuItem_menu_items.set_id LEFT JOIN exp_role_settings AS RoleSettings_role_settings ON RoleSettings_role_settings.menu_set_id = Set_menu_sets.set_id LEFT JOIN exp_menu_items AS Children_menu_items ON Children_menu_items.parent_id = MenuItem_menu_items.item_id WHERE ( RoleSettings_role_settings.role_id = 1 ) ORDER BY MenuItem_menu_items.sort, Children_menu_items.sort LIMIT 18446744073709551615 system/ee/ExpressionEngine/Controller/Publish/Edit.php L:214 EE_Menu::generate_menu() system/ee/ExpressionEngine/Service/Sidebar/Navigation/NavigationSidebar.php L:203 ExpressionEngine\Service\Sidebar\Navigation\NavigationCustomSection::render()

And then there are a couple of very slow queries:

3.5190s

43KB

SELECT COUNT(*) AS numrows FROM (exp_channel_titles as ChannelEntry_channel_titles, exp_channel_data as ChannelEntry_channel_data) LEFT JOIN exp_channel_entries_autosave AS Autosaves_channel_entries_autosave ON Autosaves_channel_entries_autosave.original_entry_id = ChannelEntry_channel_titles.entry_id LEFT JOIN exp_channels AS Channel_channels ON Channel_channels.channel_id = ChannelEntry_channel_titles.channel_id LEFT JOIN exp_members AS Author_members ON Author_members.member_id = ChannelEntry_channel_titles.author_id LEFT JOIN exp_member_data as Author_member_data ON Author_member_data.member_id = Author_members.member_id WHERE ChannelEntry_channel_data.entry_id = ChannelEntry_channel_titles.entry_id AND ( ChannelEntry_channel_data.site_id = 1 AND ChannelEntry_channel_data.channel_id = ‘7’ ) LIMIT 18446744073709551615 system/ee/ExpressionEngine/Service/EntryListing/EntryListing.php L:155 ExpressionEngine\Service\EntryListing\EntryListing::getEntryCount()

6.3938s

64KB

SELECT ChannelEntry_channel_titles.entry_id as ChannelEntry__entry_id, ChannelEntry_channel_titles.site_id as ChannelEntry__site_id, ChannelEntry_channel_titles.channel_id as ChannelEntry__channel_id, ChannelEntry_channel_titles.author_id as ChannelEntry__author_id, ChannelEntry_channel_titles.title as ChannelEntry__title, ChannelEntry_channel_titles.status as ChannelEntry__status, ChannelEntry_channel_titles.status_id as ChannelEntry__status_id, ChannelEntry_channel_titles.entry_date as ChannelEntry__entry_date, ChannelEntry_channel_titles.comment_total as ChannelEntry__comment_total, ChannelEntry_channel_data.entry_id as ChannelEntry__entry_id, ChannelEntry_channel_data.channel_id as ChannelEntry__channel_id, ChannelEntry_channel_data.site_id as ChannelEntry__site_id, ChannelEntry_channel_data.entry_id as ChannelEntry__entry_id, ChannelEntry_channel_data.site_id as ChannelEntry__site_id, ChannelEntry_channel_data.channel_id as ChannelEntry__channel_id, Autosaves_channel_entries_autosave.entry_id as Autosaves__entry_id, Autosaves_channel_entries_autosave.original_entry_id as Autosaves__original_entry_id, Autosaves_channel_entries_autosave.channel_id as Autosaves__channel_id, Autosaves_channel_entries_autosave.author_id as Autosaves__author_id, Channel_channels.channel_id as Channel__channel_id, Channel_channels.site_id as Channel__site_id, Channel_channels.channel_title as Channel__channel_title, Channel_channels.search_excerpt as Channel__search_excerpt, Channel_channels.preview_url as Channel__preview_url, Author_members.member_id as Author__member_id, Author_members.role_id as Author__role_id, Author_members.username as Author__username, Author_members.screen_name as Author__screen_name, Author_member_data.member_id as Author__member_id, Author_member_data.member_id as Author__member_id FROM (exp_channel_titles as ChannelEntry_channel_titles, exp_channel_data as ChannelEntry_channel_data) LEFT JOIN exp_channel_entries_autosave AS Autosaves_channel_entries_autosave ON Autosaves_channel_entries_autosave.original_entry_id = ChannelEntry_channel_titles.entry_id LEFT JOIN exp_channels AS Channel_channels ON Channel_channels.channel_id = ChannelEntry_channel_titles.channel_id LEFT JOIN exp_members AS Author_members ON Author_members.member_id = ChannelEntry_channel_titles.author_id LEFT JOIN exp_member_data as Author_member_data ON Author_member_data.member_id = Author_members.member_id WHERE ChannelEntry_channel_data.entry_id = ChannelEntry_channel_titles.entry_id AND ( ChannelEntry_channel_data.site_id = 1 AND ChannelEntry_channel_data.channel_id = ‘7’ ) ORDER BY ChannelEntry_channel_titles.entry_date desc LIMIT 25 L: ExpressionEngine\Controller\Publish\Edit::index()

The error is telling you the reason already:

Allowed memory size of 134217728 bytes exhausted (tried to allocate 8192 bytes) in….

This error comes from your web server PHP, not EE.

PHP memory limit is 128 MB by default. You have to increase the memory. Make sure to change it back once you migrated all the data.

Usually PHP will also timeout after the default 30 second, which means for long database operations its best to run it from the command line or again, just increase all your PHP settings while doing the long operations, once finished change them back.