1

I have problems with slow and duplicated queries, I'll need assistance, I'm working on this for more than 10h+.

Latest WP version - Latest Flatsome theme and just Query monitor and Woocommerce plugins installed.

The first slow query I wanna know more about is caller: update_metadata() - loading time 16sec

    UPDATE `wp_postmeta`
SET `meta_value` = 'a:5:{s:5:\"width\";i:550;s:6:\"height\";i:550;s:4:\"file\";s:55:\"dl/img/img_17758_af1c47d8fd932e4d88911acf423d03ca_1.jpg\";s:5:\"sizes\";a:1:{s:21:\"woocommerce_thumbnail\";a:4:{s:4:\"file\";s:56:\"img_17758_af1c47d8fd932e4d88911acf423d03ca_1-247x296.jpg\";s:5:\"width\";i:247;s:6:\"height\";i:296;s:9:\"mime-type\";s:10:\"image/jpeg\";}}s:10:\"image_meta\";a:12:{s:8:\"aperture\";s:1:\"0\";s:6:\"credit\";s:0:\"\";s:6:\"camera\";s:0:\"\";s:7:\"caption\";s:0:\"\";s:17:\"created_timestamp\";s:1:\"0\";s:9:\"copyright\";s:0:\"\";s:12:\"focal_length\";s:1:\"0\";s:3:\"iso\";s:1:\"0\";s:13:\"shutter_speed\";s:1:\"0\";s:5:\"title\";s:0:\"\";s:11:\"orientation\";s:1:\"0\";s:8:\"keywords\";a:0:{}}}'
WHERE `post_id` = 191595
AND `meta_key` = '_wp_attachment_metadata'



update_metadata()
wp-includes/meta.php:306
update_post_meta()
wp-includes/post.php:2537
wp_update_attachment_metadata()
wp-includes/post.php:6599
_wp_make_subsizes()
wp-admin/includes/image.php:452
wp_create_image_subsizes()
wp-admin/includes/image.php:368
wp_generate_attachment_metadata()
wp-admin/includes/image.php:486
WC_Regenerate_Images::resize_and_return_image()
wp-content/plugins/woocommerce/includes/class-wc-regenerate-images.php:402
WC_Regenerate_Images::maybe_resize_image()
wp-content/plugins/woocommerce/includes/class-wc-regenerate-images.php:251
apply_filters('wp_get_attachment_image_src')
wp-includes/plugin.php:189
wp_get_attachment_image_src()
wp-includes/media.php:992
wp_get_attachment_image()
wp-includes/media.php:1029
flatsome_woocommerce_get_alt_product_thumbnail()
wp-content/themes/flatsome/inc/woocommerce/structure-wc-product-box.php:68
do_action('flatsome_woocommerce_shop_loop_images')
wp-includes/plugin.php:474
load_template('wp-content/themes/flatsome/woocommerce/content-product.php')
wp-includes/template.php:772
wc_get_template_part()
wp-content/plugins/woocommerce/includes/wc-core-functions.php:284
ux_products()
wp-content/themes/flatsome/inc/shortcodes/ux_products.php:263
do_shortcode_tag()
wp-includes/shortcodes.php:356
preg_replace_callback()
wp-includes/shortcodes.php:356
do_shortcode()
wp-includes/shortcodes.php:228
apply_filters('the_content')
wp-includes/plugin.php:189
the_content()
wp-includes/post-template.php:253

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`post_id`,`meta_id`),
  UNIQUE KEY `meta_id` (`meta_id`),
  KEY `post_id` (`post_id`,`meta_key`(32),`meta_value`(32),`meta_id`),
  KEY `meta_value` (`meta_value`(32),`meta_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=InnoDB AUTO_INCREMENT=2428790 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Milan M
  • 45
  • 5
  • When you ask query-optimization questions, it's good that you included an example of the query you need help with, but it also helps to show the current `SHOW CREATE TABLE` for the table in the query, so we can see what data types, indexes, constraints, etc. are currently in the table. Also the result of `EXPLAIN` for the query, to show how MySQL is currently optimizing it. – Bill Karwin May 13 '22 at 17:53
  • Thanks for answering, I'm not really sure how to show you current SHOW CREATE TABLE – Milan M May 13 '22 at 18:10
  • In a MySQL client, you execute the SQL statement: `SHOW CREATE TABLE wp_postmeta`. Highlight the result of that statement with your mouse and then copy it to the buffer. Then click the edit button on your question above and paste the content. Please use text, not a screenshot, when sharing code. [Images are not recommended for code](https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors-when-asking-a-question/285557#285557) because if someone wants to test it, they would have to key it in manually. And vision-impaired people can't read it at all. – Bill Karwin May 13 '22 at 18:14
  • It appears an index exists starting with columns `post_id` and `meta_key` which is what I would recommend given your query. So it should be able to apply the update to one row without scanning the table. That makes it unexpected that it would take 16 seconds, unless it's running on an extremely underpowered server, or something is blocking the update. – Bill Karwin May 13 '22 at 18:39
  • I was able to speed things up by removing images (products) from homepage. Only on the homepage is this query "UPDATE `wp_postmeta" called multiple times and too slow. Now is down to 5sec. It's dedicated server with 16gb ram 4core cpu and Nginx server. – Milan M May 13 '22 at 19:09
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244726/discussion-between-milan-m-and-bill-karwin). – Milan M May 13 '22 at 19:12

1 Answers1

1

Get rid of the (32) on meta_key -- it prevents use of the subsequent columns.

Install https://wordpress.org/plugins/index-wp-mysql-for-speed/

And/or see http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Rick James
  • 135,179
  • 13
  • 127
  • 222