Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
By bmustafa on
Based on recent discussion topic that started upon coming to know about existence of a very interesting module:: https://www.drupal.org/project/compressed_cache
Whereby i have initiated a thought about going extra mile to squeeze more performance from Drupal engine (I use latest 8.8.0) - https://www.drupal.org/project/compressed_cache/issues/2962919#comment-13383032
-- The idea of enabling indexing at MYSQL for all the slow queries.
The response #7 from Andre (https://www.drupal.org/project/compressed_cache/issues/2962919#comment-13384088) has given me a lead where to start..
Steps to reproduce the outcome as per this snapshot (https://ibb.co/X2tKy4x)
*** I am using MySQL 5.7.26, so therefore, i have added the following lines to the MY.INI config file and restarted MYSQL Service
[mysqld]
default_authentication_plugin=mysql_native_password
port = 3306query_cache_size = 64M
query_cache_type=1
query_cache_limit=1048576slow_query_log = 1
slow_query_log_file = "D:/wamp64/logs/sql_query.log"
long_query_time = 1
log_queries_not_using_indexes = 1
*** then i have done some random browsing and made cache rebuilding for Drupal site --- in order to fetch those slow queries in the sql_query.log file..
*** then i have edited that file using MSWORD - in order to remove the line-wraps - with some find replace basic pattern wizardry.
*** then i have copy/pasted into excel, in order to sort and remove duplicate queries - if any.
*** then i have applied conditional formatting to show the cells containing text 'CACHE' to be highlighted in RED color.
such as::
|
SELECT tables.table_name AS table_name, tables.table_schema AS table_schema FROM INFORMATION_SCHEMA.TABLES tables WHERE (table_schema = 'workbench2020_d8') AND (table_name LIKE 'cache_%' ESCAPE '\\') AND (table_name <> 'cachetags'); DELETE FROM history WHERE timestamp < '1573312690'; |
and these are samples of non-cache tables::
| SELECT t.* FROM node__field_tmp_geofield t WHERE (entity_id IN ('3', '69', '71', '74', '75')) AND (deleted = '0') AND (langcode IN ('en', 'und', 'zxx')) ORDER BY delta ASC; # Time: 2019-12-09T15:16:30.085909Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.029974 Lock_time: 0.029357 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904590; |
| SELECT t.* FROM node__field_tmp_number_range t WHERE (entity_id IN ('3', '69', '71', '74', '75')) AND (deleted = '0') AND (langcode IN ('en', 'und', 'zxx')) ORDER BY delta ASC; # Time: 2019-12-09T15:16:30.089016Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.002258 Lock_time: 0.001861 Rows_sent: 2 Rows_examined: 4 SET timestamp=1575904590; |
| SELECT t.* FROM node__field_tmp_text_multi t WHERE (entity_id IN ('3', '69', '71', '74', '75')) AND (deleted = '0') AND (langcode IN ('en', 'und', 'zxx')) ORDER BY delta ASC; Time: 2019-12-09T15:16:30.091260Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001975 Lock_time: 0.001523 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904590; |
| SELECT t.* FROM node__field_transaction_dates_interval t WHERE (entity_id IN ('3', '69', '71', '74', '75')) AND (deleted = '0') AND (langcode IN ('en', 'und', 'zxx')) ORDER BY delta ASC; # Time: 2019-12-09T15:16:30.093567Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.002044 Lock_time: 0.001616 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904590; |
| SELECT t.* FROM node__field_transaction_status t WHERE (entity_id IN ('3', '69', '71', '74', '75')) AND (deleted = '0') AND (langcode IN ('en', 'und', 'zxx')) ORDER BY delta ASC; Time: 2019-12-09T15:16:30.095446Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001621 Lock_time: 0.001248 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904590; |
and these ...
| SELECT base_table.revision_id AS revision_id, base_table.id AS id FROM block_content base_table INNER JOIN block_content_field_data block_content_field_data ON block_content_field_data.id = base_table.id WHERE (block_content_field_data.reusable IN ('1')) AND (block_content_field_data.default_langcode IN ('1')); # Time: 2019-12-09T15:16:26.946805Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001571 Lock_time: 0.001366 Rows_sent: 8 Rows_examined: 16 SET timestamp=1575904586; |
| SELECT base_table.revision_id AS revision_id, base_table.id AS id FROM menu_link_content base_table INNER JOIN menu_link_content_data menu_link_content_data ON menu_link_content_data.id = base_table.id WHERE menu_link_content_data.rediscover = '1'; # Time: 2019-12-09T15:16:25.914647Z # User@Host: root[root] @ localhost [::1] Id: 2 # Query_time: 0.001560 Lock_time: 0.001367 Rows_sent: 6 Rows_examined: 12 SET timestamp=1575904585; |
| SELECT ces.* FROM comment_entity_statistics ces WHERE (ces.entity_id IN ('3', '69', '71', '74', '75')) AND (ces.entity_type = 'node'); # Time: 2019-12-09T15:16:30.143082Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001402 Lock_time: 0.001068 Rows_sent: 0 Rows_examined: 3 SET timestamp=1575904590; |
| SELECT ces.* FROM comment_entity_statistics ces WHERE (ces.entity_id IN ('77', '82', '83', '84', '98')) AND (ces.entity_type = 'node'); # Time: 2019-12-09T15:17:08.142236Z # User@Host: root[root] @ localhost [::1] Id: 36 # Query_time: 0.001179 Lock_time: 0.001025 Rows_sent: 0 Rows_examined: 3 SET timestamp=1575904628; |
| SELECT MAX(totalcount) AS expression FROM node_counter nc; # Time: 2019-12-09T15:18:16.657701Z # User@Host: root[root] @ localhost [::1] Id: 51 # Query_time: 0.000220 Lock_time: 0.000063 Rows_sent: 0 Rows_examined: 17 SET timestamp=1575904696; UPDATE ultimate_cron_lock SET current=lid WHERE expire <= '1575904696.6574'; # Time: 2019-12-09T15:18:17.447689Z # User@Host: root[root] @ localhost [::1] Id: 52 # Query_time: 0.017399 Lock_time: 0.005204 Rows_sent: 2114 Rows_examined: 2747 SET timestamp=1575904697; |
| SELECT menu_tree.* FROM menu_tree menu_tree WHERE (menu_name = 'admin') AND (depth >= '2') AND (depth <= '4') AND (enabled = '1') ORDER BY p1 ASC, p2 ASC, p3 ASC, p4 ASC, p5 ASC, p6 ASC, p7 ASC, p8 ASC, p9 ASC; # Time: 2019-12-09T15:16:28.970898Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.003125 Lock_time: 0.000102 Rows_sent: 380 Rows_examined: 834 SET timestamp=1575904588; |
| SELECT menu_tree.* FROM menu_tree menu_tree WHERE (p1 = '1') AND (menu_name = 'admin') AND (depth >= '2') AND (depth <= '5') AND (enabled = '1') ORDER BY p1 ASC, p2 ASC, p3 ASC, p4 ASC, p5 ASC, p6 ASC, p7 ASC, p8 ASC, p9 ASC; # Time: 2019-12-09T15:16:29.834727Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.025102 Lock_time: 0.024508 Rows_sent: 5 Rows_examined: 74 SET timestamp=1575904589; |
| SELECT name, route FROM router WHERE name IN ( 'admin_toolbar.run.cron', 'autocomplete_deluxe.autocomplete', 'big_pipe.nojs', 'block.category_autocomplete', 'block_content.add_page', 'block_content.add_form', 'entity.block_content.canonical', 'entity.block_content.edit_form', 'entity.block_content.delete_form', 'book.render', 'book.export', 'entity.node.book_outline_form', 'entity.node.book_remove_form', 'charts_api_example.display', 'entity.comment.edit_form', 'comment.approve', 'entity.comment.canonical', 'entity.comment.delete_form', 'comment.reply', 'comment.new_comments_node_links', 'comment.node_redirect', 'contact.site_page', 'entity.contact_form.canonical', 'entity.user.contact_form', 'contextual.render', 'country.autocomplete', 'devel.reinstall', 'devel.menu_rebuild', 'devel.configs_list', 'devel.config_edit', 'devel.config_delete', 'devel.state_system_page', 'devel.system_state_edit', 'devel.theme_registry', 'devel.field_info_page', 'devel.session', 'devel.switch', 'devel.cache_clear', 'devel.run_cron', 'devel.container_info.service', 'devel.container_info.service.detail', 'devel.container_info.parameter', 'devel.container_info.parameter.detail', 'devel.route_info', 'devel.route_info.item', 'devel.event_info', 'devel.layout_info', 'devel.elements_page', 'devel.elements_page.detail', 'devel.entity_info_page', 'devel.entity_info_page.detail', 'devel.entity_info_page.fields', 'diff.revisions_diff', 'editor.filter_xss', 'editor.field_untransformed_text', 'editor.image_dialog', 'editor.link_dialog', 'entity_browser.browse_bank_accounts', 'entity_browser.edit_form', 'entity_reference_ajax_formatter.ajax_field', 'existing_values_autocomplete_widget.autocomplete', 'file.ajax_progress', 'filter.tips_all', 'filter.tips', 'flot_axislabels_examples.example', 'flot_examples.content', 'flot_examples.basic_usage', 'flot_examples.series_types', 'flot_examples.categories', 'flot_examples.basic_options', 'flot_examples.annotating', 'flot_examples.ajax', 'flot_examples.json1_empty', 'flot_examples.json1', 'flot_examples.json2_empty', 'flot_examples.json2', 'flot_examples.json3_empty', 'flot_examples.json3', 'flot_examples.realtime', 'flot_examples.series_toggle', 'flot_examples.selection', 'flot_examples.zooming', 'flot_examples.interacting', 'flot_examples.navigate', 'flot_examples.resize', 'flot_examples.symbols', 'flot_examples.axes-time', 'flot_examples.visitors', 'flot_examples.axes-multiple', 'flot_examples.axes_interacting', 'flot_examples.threshold', 'flot_examples.stacking', 'flot_examples.percentiles', 'flot_examples.tracking', 'flot_examples.image', 'flot_examples.series_errorbars', 'flot_examples.series_pie', 'flot_examples.canvas', 'flot_spider_examples.example', 'fullcalendar_view.update_event', 'fullcalendar_view.add_event', 'geofield_map.ajax_popup', 'history.get_last_node_view', 'history.read_node', 'image.style_public', 'image.style_private', 'image.upload', 'image.info', 'imce.page', 'jsonapi.backup_migrate_destination--backup_migrate_destination.collection', 'jsonapi.backup_migrate_destination--backup_migrate_destination.individual', 'jsonapi.backup_migrate_schedule--backup_migrate_schedule.collection', 'jsonapi.backup_migrate_schedule--backup_migrate_schedule.individual', 'jsonapi.backup_migrate_settings--backup_migrate_settings.collection', 'jsonapi.backup_migrate_settings--backup_migrate_settings.individual', 'jsonapi.backup_migrate_source--backup_migrate_source.collection', 'jsonapi.backup_migrate_source--backup_migrate_source.individual', 'jsonapi.block--block.collection', 'jsonapi.block--block.individual', 'jsonapi.block_content--basic.collection', 'jsonapi.block_content--basic.collection.post', 'jsonapi.block_content--basic.individual', 'jsonapi.block_content--basic.individual.patch', 'jsonapi.block_content--basic.individual.delete', 'jsonapi.block_content--basic.block_content_type.relationship.get', 'jsonapi.block_content--basic.block_content_type.relationship.post', 'jsonapi.block_content--basic.block_content_type.relationship.patch', 'jsonapi.block_content--basic.block_content_type.relationship.delete', 'jsonapi.block_content--basic.block_content_type.related', 'jsonapi.block_content--basic.revision_user.relationship.get', 'jsonapi.block_content--basic.revision_user.relationship.post', 'jsonapi.block_content--basic.revision_user.relationship.patch', 'jsonapi.block_content--basic.revision_user.relationship.delete', 'jsonapi.block_content--basic.revision_user.related', 'jsonapi.block_content--image.collection', 'jsonapi.block_content--image.collection.post', 'jsonapi.block_content--image.individual', 'jsonapi.block_content--image.individual.patch', 'jsonapi.block_content--image.individual.delete', 'jsonapi.block_content--image.block_content_type.relationship.get', 'jsonapi.block_content--image.block_content_type.relationship.post', 'jsonapi.block_content--image.block_content_type.relationship.patch', 'jsonapi.block_content--image.block_content_type.relationship.delete', 'jsonapi.block_content--image.block_content_type.related', 'jsonapi.block_content--image.revision_user.relationship.get', 'jsonapi.block_content--image.revision_user.relationship.post', 'jsonapi.block_content--image.revision_user.relationship.patch', 'jsonapi.block_content--image.revision_user.relationship.delete', 'jsonapi.block_content--image.revision_user.related', 'jsonapi.block_content--image.field_image.relationship.get', 'jsonapi.block_content--image.field_image.relationship.post', 'jsonapi.block_content--image.field_image.relationship.patch', 'jsonapi.block_content--image.field_image.relationship.delete', 'jsonapi.block_content--image.field_image.related', 'jsonapi.block_content--layout.collection', 'jsonapi.block_content--layout.collection.post', 'jsonapi.block_content--layout.individual', 'jsonapi.block_content--layout.individual.patch', 'jsonapi.block_content--layout.individual.delete', 'jsonapi.block_content--layout.block_content_type.relationship.get', 'jsonapi.block_content--layout.block_content_type.relationship.post', 'jsonapi.block_content--layout.block_content_type.relationship.patch', 'jsonapi.block_content--layout.block_content_type.relationship.delete', 'jsonapi.block_content--layout.block_content_type.related', 'jsonapi.block_content--layout.revision_user.relationship.get', 'jsonapi.block_content--layout.revision_user.relationship.post', 'jsonapi.block_content--layout.revision_user.relationship.patch', 'jsonapi.block_content--layout.revision_user.relationship.delete', 'jsonapi.block_content--layout.revision_user.related', 'jsonapi.block_content--wrapper.collection', 'jsonapi.block_content--wrapper.collection.post', 'jsonapi.block_content--wrapper.individual', 'jsonapi.block_content--wrapper.individual.patch', 'jsonapi.block_content--wrapper.individual.delete', 'jsonapi.block_content--wrapper.block_content_type.relationship.get', 'jsonapi.block_content--wrapper.block_content_type.relationship.post', 'jsonapi.block_content--wrapper.block_content_type.relationship.patch', 'jsonapi.block_content--wrapper.block_content_type.relationship.delete', 'jsonapi.block_content--wrapper.block_content_type.related', 'jsonapi.block_content--wrapper.revision_user.relationship.get', 'jsonapi.block_content--wrapper.revision_user.relationship.post', 'jsonapi.block_content--wrapper.revision_user.relationship.patch', 'jsonapi.block_content--wrapper.revision_user.relationship.delete', 'jsonapi.block_content--wrapper.revision_user.related', 'jsonapi.block_content_type--block_content_type.collection', 'jsonapi.block_content_type--block_content_type.individual', 'jsonapi.corresponding_reference--corresponding_reference.collection', 'jsonapi.corresponding_reference--corresponding_reference.individual', 'jsonapi.comment--comment.collection', 'jsonapi.comment--comment.collection.post', 'jsonapi.comment--comment.individual', 'jsonapi.comment--comment.individual.patch', 'jsonapi.comment--comment.individual.delete', 'jsonapi.comment--comment.comment_type.relationship.get', 'jsonapi.comment--comment.comment_type.relationship.post', 'jsonapi.comment--comment.comment_type.relationship.patch', 'jsonapi.comment--comment.comment_type.relationship.delete', 'jsonapi.comment--comment.comment_type.related', 'jsonapi.comment--comment.uid.relationship.get', 'jsonapi.comment--comment.uid.relationship.post', 'jsonapi.comment--comment.uid.relationship.patch', 'jsonapi.comment--comment.uid.relationship.delete', 'jsonapi.comment--comment.uid.related', 'jsonapi.comment--comment.pid.relationship.get', 'jsonapi.comment--comment.pid.relationship.post', 'jsonapi.comment--comment.pid.relationship.patch', 'jsonapi.comment--comment.pid.relationship.delete', 'jsonapi.comment--comment.pid.related', 'jsonapi.comment--comment.entity_id.relationship.get', 'jsonapi.comment--comment.entity_id.relationship.post', 'jsonapi.comment--comment.entity_id.relationship.patch', 'jsonapi.comment--comment.entity_id.relationship.delete', 'jsonapi.comment--comment.entity_id.related', 'jsonapi.comment_type--comment_type.collection', 'jsonapi.comment_type--comment_type.individual', 'jsonapi.contact_form--contact_form.collection', 'jsonapi.contact_form--contact_form.individual', 'jsonapi.contact_message--feedback.collection.post', 'jsonapi.contact_message--personal.collection.post', 'jsonapi.editor--editor.collection', 'jsonapi.editor--editor.individual', 'jsonapi.entity_browser--entity_browser.collection', 'jsonapi.entity_browser--entity_browser.individual', 'jsonapi.field_config--field_config.collection', 'jsonapi.field_config--field_config.individual', 'jsonapi.field_storage_config--field_storage_config.collection', 'jsonapi.field_storage_config--field_storage_config.individual', 'jsonapi.file--file.collection', 'jsonapi.file--file.collection.post', 'jsonapi.file--file.individual', 'jsonapi.file--file.individual.patch', 'jsonapi.file--file.individual.delete', 'jsonapi.file--file.uid.relationship.get', 'jsonapi.file--file.uid.relationship.post', 'jsonapi.file--file.uid.relationship.patch', 'jsonapi.file--file.uid.relationship.delete', 'jsonapi.file--file.uid.related', 'jsonapi.filter_format--filter_format.collection', 'jsonapi.filter_format--filter_format.individual', 'jsonapi.image_style--image_style.collection', 'jsonapi.image_style--image_style.individual', 'jsonapi.imce_profile--imce_profile.collection', 'jsonapi.imce_profile--imce_profile.individual', 'jsonapi.library_definition--library_definition.collection', 'jsonapi.library_definition--library_definition.individual', 'jsonapi.linkit_profile--linkit_profile.collection', 'jsonapi.linkit_profile--linkit_profile.individual', 'jsonapi.media--audio.collection', 'jsonapi.media--audio.collection.post', 'jsonapi.media--audio.individual', 'jsonapi.media--audio.individual.patch', 'jsonapi.media--audio.individual.delete', 'jsonapi.media--audio.bundle.relationship.get', 'jsonapi.media--audio.bundle.relationship.post', 'jsonapi.media--audio.bundle.relationship.patch', 'jsonapi.media--audio.bundle.relationship.delete', 'jsonapi.media--audio.bundle.related', 'jsonapi.media--audio.revision_user.relationship.get', 'jsonapi.media--audio.revision_user.relationship.post', 'jsonapi.media--audio.revision_user.relationship.patch', 'jsonapi.media--audio.revision_user.relationship.delete', 'jsonapi.media--audio.revision_user.related', 'jsonapi.media--audio.uid.relationship.get', 'jsonapi.media--audio.uid.relationship.post', 'jsonapi.media--audio.uid.relationship.patch', 'jsonapi.media--audio.uid.relationship.delete', 'jsonapi.media--audio.uid.related', 'jsonapi.media--audio.thumbnail.relationship.get', 'jsonapi.media--audio.thumbnail.relationship.post', 'jsonapi.media--audio.thumbnail.relationship.patch', 'jsonapi.media--audio.thumbnail.relationship.delete', 'jsonapi.media--audio.thumbnail.related', 'jsonapi.media--audio.field_media_audio_file.relationship.get', 'jsonapi.media--audio.field_media_audio_file.relationship.post', 'jsonapi.media--audio.field_media_audio_file.relationship.patch', 'jsonapi.media--audio.field_media_audio_file.relationship.delete', 'jsonapi.media--audio.field_media_audio_file.related', 'jsonapi.media--file.collection', 'jsonapi.media--file.collection.post', 'jsonapi.media--file.individual', 'jsonapi.media--file.individual.patch', 'jsonapi.media--file.individual.delete', 'jsonapi.media--file.bundle.relationship.get', 'jsonapi.media--file.bundle.relationship.post', 'jsonapi.media--file.bundle.relationship.patch', 'jsonapi.media--file.bundle.relationship.delete', 'jsonapi.media--file.bundle.related', 'jsonapi.media--file.revision_user.relationship.get', 'jsonapi.media--file.revision_user.relationship.post', 'jsonapi.media--file.revision_user.relationship.patch', 'jsonapi.media--file.revision_user.relationship.delete', 'jsonapi.media--file.revision_user.related', 'jsonapi.media--file.uid.relationship.get', 'jsonapi.media--file.uid.relationship.post', 'jsonapi.media--file.uid.relationship.patch', 'jsonapi.media--file.uid.relationship.delete', 'jsonapi.media--file.uid.related', 'jsonapi.media--file.thumbnail.relationship.get', 'jsonapi.media--file.thumbnail.relationship.post', 'jsonapi.media--file.thumbnail.relationship.patch', 'jsonapi.media--file.thumbnail.relationship.delete', 'jsonapi.media--file.thumbnail.related', 'jsonapi.media--file.field_media_file.relationship.get', 'jsonapi.media--file.field_media_file.relationship.post', 'jsonapi.media--file.field_media_file.relationship.patch', 'jsonapi.media--file.field_media_file.relationship.delete', 'jsonapi.media--file.field_media_file.related', 'jsonapi.media--image.collection', 'jsonapi.media--image.collection.post', 'jsonapi.media--image.individual', 'jsonapi.media--image.individual.patch', 'jsonapi.media--image.individual.delete', 'jsonapi.media--image.bundle.relationship.get', 'jsonapi.media--image.bundle.relationship.post', 'jsonapi.media--image.bundle.relationship.patch', 'jsonapi.media--image.bundle.relationship.delete', 'jsonapi.media--image.bundle.related', 'jsonapi.media--image.revision_user.relationship.get', 'jsonapi.media--image.revision_user.relationship.post', 'jsonapi.media--image.revision_user.relationship.patch', 'jsonapi.media--image.revision_user.relationship.delete', 'jsonapi.media--image.revision_user.related', 'jsonapi.media--image.uid.relationship.get', 'jsonapi.media--image.uid.relationship.post', 'jsonapi.media--image.uid.relationship.patch', 'jsonapi.media--image.uid.relationship.delete', 'jsonapi.media--image.uid.related', 'jsonapi.media--image.thumbnail.relationship.get', 'jsonapi.media--image.thumbnail.relationship.post', 'jsonapi.media--image.thumbnail.relationship.patch', 'jsonapi.media--image.thumbnail.relationship.delete', 'jsonapi.media--image.thumbnail.related', 'jsonapi.media--image.field_media_image.relationship.get', 'jsonapi.media--image.field_media_image.relationship.post', 'jsonapi.media--image.field_media_image.relationship.patch', 'jsonapi.media--image.field_media_image.relationship.delete', 'jsonapi.media--image.field_media_image.related', 'jsonapi.media--remote_video.collection', 'jsonapi.media--remote_video.collection.post', 'jsonapi.media--remote_video.individual', 'jsonapi.media--remote_video.individual.patch', 'jsonapi.media--remote_video.individual.delete', 'jsonapi.media--remote_video.bundle.relationship.get', 'jsonapi.media--remote_video.bundle.relationship.post', 'jsonapi.media--remote_video.bundle.relationship.patch', 'jsonapi.media--remote_video.bundle.relationship.delete', 'jsonapi.media--remote_video.bundle.related', 'jsonapi.media--remote_video.revision_user.relationship.get', 'jsonapi.media--remote_video.revision_user.relationship.post', 'jsonapi.media--remote_video.revision_user.relationship.patch', 'jsonapi.media--remote_video.revision_user.relationship.delete', 'jsonapi.media--remote_video.revision_user.related', 'jsonapi.media--remote_video.uid.relationship.get', 'jsonapi.media--remote_video.uid.relationship.post', 'jsonapi.media--remote_video.uid.relationship.patch', 'jsonapi.media--remote_video.uid.relationship.delete', 'jsonapi.media--remote_video.uid.related', 'jsonapi.media--remote_video.thumbnail.relationship.get', 'jsonapi.media--remote_video.thumbnail.relationship.post', 'jsonapi.media--remote_video.thumbnail.relationship.patch', 'jsonapi.media--remote_video.thumbnail.relationship.delete', 'jsonapi.media--remote_video.thumbnail.related', 'jsonapi.media--video.collection', 'jsonapi.media--video.collection.post', 'jsonapi.media--video.individual', 'jsonapi.media--video.individual.patch', 'jsonapi.media--video.individual.delete', 'jsonapi.media--video.bundle.relationship.get', 'jsonapi.media--video.bundle.relationship.post', 'jsonapi.media--video.bundle.relationship.patch', 'jsonapi.media--video.bundle.relationship.delete', 'jsonapi.media--video.bundle.related', 'jsonapi.media--video.revision_user.relationship.get', 'jsonapi.media--video.revision_user.relationship.post', 'jsonapi.media--video.revision_user.relationship.patch', 'jsonapi.media--video.revision_user.relationship.delete', 'jsonapi.media--video.revision_user.related', 'jsonapi.media--video.uid.relationship.get', 'jsonapi.media--video.uid.relationship.post', 'jsonapi.media--video.uid.relationship.patch', 'jsonapi.media--video.uid.relationship.delete', 'jsonapi.media--video.uid.related', 'jsonapi.media--video.thumbnail.relationship.get', 'jsonapi.media--video.thumbnail.relationship.post', 'jsonapi.media--video.thumbnail.relationship.patch', 'jsonapi.media--video.thumbnail.relationship.delete', 'jsonapi.media--video.thumbnail.related', 'jsonapi.media--video.field_media_video_file.relationship.get', 'jsonapi.media--video.field_media_video_file.relationship.post', 'jsonapi.media--video.field_media_video_file.relationship.patch', 'jsonapi.media--video.field_media_video_file.relationship.delete', 'jsonapi.media--video.field_media_video_file.related', 'jsonapi.media_type--media_type.collection', 'jsonapi.media_type--media_type.individual', 'jsonapi.node--article.collection', 'jsonapi.node--article.collection.post', 'jsonapi.node--article.individual', 'jsonapi.node--article.individual.patch', 'jsonapi.node--article.individual.delete', 'jsonapi.node--article.node_type.relationship.get', 'jsonapi.node--article.node_type.relationship.post', 'jsonapi.node--article.node_type.relationship.patch', 'jsonapi.node--article.node_type.relationship.delete', 'jsonapi.node--article.node_type.related', 'jsonapi.node--article.revision_uid.relationship.get', 'jsonapi.node--article.revision_uid.relationship.post', 'jsonapi.node--article.revision_uid.relationship.patch', 'jsonapi.node--article.revision_uid.relationship.delete', 'jsonapi.node--article.revision_uid.related', 'jsonapi.node--article.uid.relationship.get', 'jsonapi.node--article.uid.relationship.post', 'jsonapi.node--article.uid.relationship.patch', 'jsonapi.node--article.uid.relationship.delete', 'jsonapi.node--article.uid.related', 'jsonapi.node--article.menu_link.relationship.get', 'jsonapi.node--article.menu_link.relationship.post', 'jsonapi.node--article.menu_link.relationship.patch', 'jsonapi.node--article.menu_link.relationship.delete', 'jsonapi.node--article.menu_link.related', 'jsonapi.node--article.field_image.relationship.get', 'jsonapi.node--article.field_image.relationship.post', 'jsonapi.node--article.field_image.relationship.patch', 'jsonapi.node--article.field_image.relationship.delete', 'jsonapi.node--article.field_image.related', 'jsonapi.node--article.field_tags.relationship.get', 'jsonapi.node--article.field_tags.relationship.post', 'jsonapi.node--article.field_tags.relationship.patch', 'jsonapi.node--article.field_tags.relationship.delete', 'jsonapi.node--article.field_tags.related', 'jsonapi.node--book.collection', 'jsonapi.node--book.collection.post', 'jsonapi.node--book.individual', 'jsonapi.node--book.individual.patch', 'jsonapi.node--book.individual.delete', 'jsonapi.node--book.node_type.relationship.get', 'jsonapi.node--book.node_type.relationship.post', 'jsonapi.node--book.node_type.relationship.patch', 'jsonapi.node--book.node_type.relationship.delete', 'jsonapi.node--book.node_type.related', 'jsonapi.node--book.revision_uid.relationship.get', 'jsonapi.node--book.revision_uid.relationship.post', 'jsonapi.node--book.revision_uid.relationship.patch', 'jsonapi.node--book.revision_uid.relationship.delete', 'jsonapi.node--book.revision_uid.related', 'jsonapi.node--book.uid.relationship.get', 'jsonapi.node--book.uid.relationship.post', 'jsonapi.node--book.uid.relationship.patch', 'jsonapi.node--book.uid.relationship.delete', 'jsonapi.node--book.uid.related', 'jsonapi.node--book.menu_link.relationship.get', 'jsonapi.node--book.menu_link.relationship.post', 'jsonapi.node--book.menu_link.relationship.patch', 'jsonapi.node--book.menu_link.relationship.delete', 'jsonapi.node--book.menu_link.related', 'jsonapi.node--book.field_file.relationship.get', 'jsonapi.node--book.field_file.relationship.post', 'jsonapi.node--book.field_file.relationship.patch', 'jsonapi.node--book.field_file.relationship.delete', 'jsonapi.node--book.field_file.related', 'jsonapi.node--book.field_image_s_.relationship.get', 'jsonapi.node--book.field_image_s_.relationship.post', 'jsonapi.node--book.field_image_s_.relationship.patch', 'jsonapi.node--book.field_image_s_.relationship.delete', 'jsonapi.node--book.field_image_s_.related', 'jsonapi.node--bundle_of_attachments_banks_.collection', 'jsonapi.node--bundle_of_attachments_banks_.collection.post', 'jsonapi.node--bundle_of_attachments_banks_.individual', 'jsonapi.node--bundle_of_attachments_banks_.individual.patch', 'jsonapi.node--bundle_of_attachments_banks_.individual.delete', 'jsonapi.node--bundle_of_attachments_banks_.node_type.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.node_type.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.node_type.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.node_type.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.node_type.related', 'jsonapi.node--bundle_of_attachments_banks_.revision_uid.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.revision_uid.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.revision_uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.revision_uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.revision_uid.related', 'jsonapi.node--bundle_of_attachments_banks_.uid.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.uid.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.uid.related', 'jsonapi.node--bundle_of_attachments_banks_.menu_link.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.menu_link.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.menu_link.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.menu_link.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.menu_link.related', 'jsonapi.node--bundle_of_attachments_banks_.field_attachment.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.field_attachment.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.field_attachment.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.field_attachment.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.field_attachment.related', 'jsonapi.node--bundle_of_attachments_banks_.field_parent_entity_id.relationship.get', 'jsonapi.node--bundle_of_attachments_banks_.field_parent_entity_id.relationship.post', 'jsonapi.node--bundle_of_attachments_banks_.field_parent_entity_id.relationship.patch', 'jsonapi.node--bundle_of_attachments_banks_.field_parent_entity_id.relationship.delete', 'jsonapi.node--bundle_of_attachments_banks_.field_parent_entity_id.related', 'jsonapi.node--bundle_of_attachments_company_.collection', 'jsonapi.node--bundle_of_attachments_company_.collection.post', 'jsonapi.node--bundle_of_attachments_company_.individual', 'jsonapi.node--bundle_of_attachments_company_.individual.patch', 'jsonapi.node--bundle_of_attachments_company_.individual.delete', 'jsonapi.node--bundle_of_attachments_company_.node_type.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.node_type.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.node_type.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.node_type.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.node_type.related', 'jsonapi.node--bundle_of_attachments_company_.revision_uid.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.revision_uid.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.revision_uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.revision_uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.revision_uid.related', 'jsonapi.node--bundle_of_attachments_company_.uid.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.uid.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.uid.related', 'jsonapi.node--bundle_of_attachments_company_.menu_link.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.menu_link.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.menu_link.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.menu_link.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.menu_link.related', 'jsonapi.node--bundle_of_attachments_company_.field_attachment.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.field_attachment.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.field_attachment.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.field_attachment.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.field_attachment.related', 'jsonapi.node--bundle_of_attachments_company_.field_parent_entity_id.relationship.get', 'jsonapi.node--bundle_of_attachments_company_.field_parent_entity_id.relationship.post', 'jsonapi.node--bundle_of_attachments_company_.field_parent_entity_id.relationship.patch', 'jsonapi.node--bundle_of_attachments_company_.field_parent_entity_id.relationship.delete', 'jsonapi.node--bundle_of_attachments_company_.field_parent_entity_id.related', 'jsonapi.node--bundle_of_attachments_digital_su.collection', 'jsonapi.node--bundle_of_attachments_digital_su.collection.post', 'jsonapi.node--bundle_of_attachments_digital_su.individual', 'jsonapi.node--bundle_of_attachments_digital_su.individual.patch', 'jsonapi.node--bundle_of_attachments_digital_su.individual.delete', 'jsonapi.node--bundle_of_attachments_digital_su.node_type.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.node_type.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.node_type.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.node_type.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.node_type.related', 'jsonapi.node--bundle_of_attachments_digital_su.revision_uid.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.revision_uid.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.revision_uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.revision_uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.revision_uid.related', 'jsonapi.node--bundle_of_attachments_digital_su.uid.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.uid.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.uid.related', 'jsonapi.node--bundle_of_attachments_digital_su.menu_link.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.menu_link.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.menu_link.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.menu_link.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.menu_link.related', 'jsonapi.node--bundle_of_attachments_digital_su.field_attachment.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.field_attachment.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.field_attachment.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.field_attachment.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.field_attachment.related', 'jsonapi.node--bundle_of_attachments_digital_su.field_parent_entity_id.relationship.get', 'jsonapi.node--bundle_of_attachments_digital_su.field_parent_entity_id.relationship.post', 'jsonapi.node--bundle_of_attachments_digital_su.field_parent_entity_id.relationship.patch', 'jsonapi.node--bundle_of_attachments_digital_su.field_parent_entity_id.relationship.delete', 'jsonapi.node--bundle_of_attachments_digital_su.field_parent_entity_id.related', 'jsonapi.node--bundle_of_attachments_persons_.collection', 'jsonapi.node--bundle_of_attachments_persons_.collection.post', 'jsonapi.node--bundle_of_attachments_persons_.individual', 'jsonapi.node--bundle_of_attachments_persons_.individual.patch', 'jsonapi.node--bundle_of_attachments_persons_.individual.delete', 'jsonapi.node--bundle_of_attachments_persons_.node_type.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.node_type.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.node_type.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.node_type.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.node_type.related', 'jsonapi.node--bundle_of_attachments_persons_.revision_uid.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.revision_uid.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.revision_uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.revision_uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.revision_uid.related', 'jsonapi.node--bundle_of_attachments_persons_.uid.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.uid.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.uid.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.uid.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.uid.related', 'jsonapi.node--bundle_of_attachments_persons_.menu_link.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.menu_link.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.menu_link.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.menu_link.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.menu_link.related', 'jsonapi.node--bundle_of_attachments_persons_.field_attachment.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.field_attachment.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.field_attachment.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.field_attachment.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.field_attachment.related', 'jsonapi.node--bundle_of_attachments_persons_.field_parent_entity_id.relationship.get', 'jsonapi.node--bundle_of_attachments_persons_.field_parent_entity_id.relationship.post', 'jsonapi.node--bundle_of_attachments_persons_.field_parent_entity_id.relationship.patch', 'jsonapi.node--bundle_of_attachments_persons_.field_parent_entity_id.relationship.delete', 'jsonapi.node--bundle_of_attachments_persons_.field_parent_entity_id.related', 'jsonapi.node--bundle_of_attachments_ports_.collection', 'jsonapi.node--bundle_of_attachments_ports_.collection.post', 'jsonapi.node--bundle_of_attachments_ports_.individual', 'jsonapi.node--bundle_of_attachments_ports_.individual.patch', 'jsonapi.node--bundle_of_attachments_ports_.individual.delete', 'jsonapi.node--bundle_of_attachments_ports_.node_type.relationship.get', 'jsonapi.node--bundle_of_attachments_ports_.node_type.relationship.post', 'jsonapi.node--bundle_of_attachments_ports_.node_type.relationship.patch', 'jsonapi.node--bundle_of_attachments_ports_.node_type.relationship.delete', 'jsonapi.node--bundle_of_attachments_ports_.node_type.related', 'jsonapi.node--bundle_of_attachments_ports_.revision_uid.relationship.get', 'jsonapi.node--bundle_of_attachments_ports_.revision_uid.relationship.post', 'jsonapi.node--bundle_of_attachments_ports_.revision_uid.relationship.patch', 'jsonapi.node--bundle_ |
| SELECT node_field_data.changed AS node_field_data_changed, node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid, 'content_recent:block_1' AS view_name FROM node_field_data node_field_data INNER JOIN users_field_data users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid WHERE (node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1) ORDER BY node_field_data_changed DESC LIMIT 5 OFFSET 0; # Time: 2019-12-09T15:16:29.871089Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.027528 Lock_time: 0.027043 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904589; |
| SELECT node_field_data.changed AS node_field_data_changed, node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid, 'content_recent:block_1' AS view_name FROM node_field_data node_field_data INNER JOIN users_field_data users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid WHERE (node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1 OR 1 = 1) ORDER BY node_field_data_changed DESC LIMIT 5 OFFSET 0; # Time: 2019-12-09T15:18:11.303485Z # User@Host: root[root] @ localhost [::1] Id: 51 # Query_time: 0.005000 Lock_time: 0.000171 Rows_sent: 17 Rows_examined: 17 SET timestamp=1575904691; |
| SELECT revision.*, data.menu_name AS menu_name, data.rediscover AS rediscover, data.weight AS weight, data.expanded AS expanded, data.parent AS parent FROM menu_link_content_field_revision revision LEFT OUTER JOIN menu_link_content_data data ON (revision.id = data.id and revision.langcode = data.langcode) WHERE (revision.id IN ('1', '2', '3', '4', '5', '6')) AND (revision.revision_id IN ('1', '2', '3', '4', '5', '6')) ORDER BY revision.id ASC; # Time: 2019-12-09T15:16:26.485031Z # User@Host: root[root] @ localhost [::1] Id: 2 # Query_time: 0.005028 Lock_time: 0.001318 Rows_sent: 0 Rows_examined: 454 SET timestamp=1575904586; |
| SELECT revision.*, data.reusable AS reusable FROM block_content_field_revision revision LEFT OUTER JOIN block_content_field_data data ON (revision.id = data.id and revision.langcode = data.langcode) WHERE (revision.id IN ('1', '2', '3', '4', '6', '7', '8', '9')) AND (revision.revision_id IN ('1', '2', '3', '4', '6', '7', '8', '9')) ORDER BY revision.id ASC; # Time: 2019-12-09T15:16:26.953323Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001531 Lock_time: 0.001105 Rows_sent: 1 Rows_examined: 2 SET timestamp=1575904586; |
| SELECT revision.*, data.weight AS weight FROM taxonomy_term_field_revision revision LEFT OUTER JOIN taxonomy_term_field_data data ON (revision.tid = data.tid and revision.langcode = data.langcode) WHERE (revision.tid IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72')) AND (revision.revision_id IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '62', '63', '64', '65', '66', '67', '68', '69', '70', '71', '72')) ORDER BY revision.tid ASC; # Time: 2019-12-09T15:17:08.628639Z # User@Host: root[root] @ localhost [::1] Id: 36 # Query_time: 0.000506 Lock_time: 0.000099 Rows_sent: 71 Rows_examined: 190 SET timestamp=1575904628; |
| SELECT revision.revision_id AS revision_id, revision.langcode AS langcode, revision.revision_user AS revision_user, revision.revision_created AS revision_created, revision.revision_log AS revision_log, revision.revision_default AS revision_default, base.id AS id, base.type AS type, base.uuid AS uuid, CASE base.revision_id WHEN revision.revision_id THEN 1 ELSE 0 END AS isDefaultRevision FROM block_content base INNER JOIN block_content_revision revision ON revision.revision_id = base.revision_id WHERE base.id IN ('1', '2', '3', '4', '6', '7', '8', '9'); # Time: 2019-12-09T15:16:26.949119Z # User@Host: root[root] @ localhost [::1] Id: 3 # Query_time: 0.001895 Lock_time: 0.001246 Rows_sent: 8 Rows_examined: 24 SET timestamp=1575904586; |
===========
Observations:
- In total, - have fetched 45 slow queries in the Sql_query.log file, that gives me a clue on what needs to be indexed,,, by attacking the columns that are mentioned right after the WHERE clause.
- The results will vastly vary per user, depending on his own specific drupal adaptation.
- also noticed that drupal is already having the indices applied - and realized that these slow queries are inevitable.
