MYSQL Query Performance Issue

MYSQL Query Performance Issue
0
#1

Hey there,
I am working with a Wordpress / WooCommerce installation and having performance issues with several MySQL Querys. The pages with these query are loading really slow: 5-15 minutes… (it has been slow before, but since about 2 weeks it’s really slow. but nothing really changed then as I remember)
Maybe the reason could be the MySQL query itself. If someone finds time to have a look at it and have an idea to make this much faster? :slight_smile:

$taxes = $wpdb->get_results( 'SELECT StoreID, StoreTitle, TaxClass, SUM(LineTotal) as TotalSum, SUM(LineTax) as TotalTax FROM (
SELECT `i`.`order_id` AS `OrderID`,
       `i`.`order_item_id` AS `OrderItemID`,
       @productID := (SELECT MAX(
                CASE 
                    WHEN `meta_key` = "_tax_class"
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND `meta_key` = "_tax_class") AS TaxClass,
    
		(SELECT MAX(
                CASE 
                    WHEN `meta_key` = "_product_id"
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND `meta_key` = "_product_id") AS ProductID,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_line_tax" 
                       THEN `meta_value` 
                       ELSE NULL 
                   END) 
          FROM `wp_woocommerce_order_itemmeta`
         WHERE `order_item_id` = `i`.`order_item_id`
           AND `meta_key` = "_line_tax") AS LineTax,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_line_total"
                       THEN `meta_value` 
                       ELSE NULL 
                   END) 
          FROM `wp_woocommerce_order_itemmeta`
         WHERE `order_item_id` = `i`.`order_item_id`
           AND `meta_key` = "_line_total") AS LineTotal,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_completed_date"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_completed_date") AS Date,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos") AS POS,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store") AS StoreID,
(SELECT
  MAX(
    CASE WHEN meta_key = "_payment_method" THEN meta_value ELSE NULL
  END)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_payment_method"
) AS PaymentMethod,
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store_title" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store_title") AS StoreTitle,
    
       (SELECT post_status
          FROM `wp_posts`
         WHERE `ID` = `i`.`order_id`) AS Status

 FROM `wp_woocommerce_order_items` AS `i` 
  WHERE `i`.`order_item_type` = "line_item"
GROUP BY OrderItemID, TaxClass
ORDER BY OrderID DESC) as Taxes
WHERE StoreID = '.$store_id.' AND
	  date(Date) = curdate() AND
      ProductID != "20548" AND
	  ProductID != "20543" AND
      Status = "wc-completed" AND
	  POS = "1" AND
      PaymentMethod != "cod" AND
	  PaymentMethod != "cheque"
GROUP BY StoreID, TaxClass');

	$paym_methods = $wpdb->get_results('SELECT
  StoreID,
  StoreTitle,
  PaymentMethod,
  PaymentMethodTitle,
  SUM(Total) as TotalSum
FROM
  (
  SELECT
    i.order_id AS OrderID,
    i.order_item_id,
    @productID :=(
    SELECT
      MAX(
        CASE WHEN meta_key = "_order_total" THEN meta_value ELSE NULL
      END
  )
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_order_total"
) AS Total,
(
SELECT
  MAX(
    CASE WHEN meta_key = "_payment_method" THEN meta_value ELSE NULL
  END
)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_payment_method"
) AS PaymentMethod,
    
		(SELECT MAX(
                CASE 
                    WHEN `meta_key` = "_product_id"
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND `meta_key` = "_product_id") AS ProductID,
(
  SELECT
    MAX(
      CASE WHEN meta_key = "_payment_method_title" THEN meta_value ELSE NULL
    END
)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_payment_method_title"
) AS PaymentMethodTitle,
(
  SELECT
    MAX(
      CASE WHEN meta_key = "_completed_date" THEN meta_value ELSE NULL
    END
)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_completed_date"
) AS DATE,
(
  SELECT
    MAX(
      CASE WHEN meta_key = "_pos_store" THEN meta_value ELSE NULL
    END
)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_pos_store"
) AS StoreID,
(
  SELECT
    MAX(
      CASE WHEN meta_key = "_pos_store_title" THEN meta_value ELSE NULL
    END
)
FROM
  wp_postmeta
WHERE
  post_id = i.order_id AND meta_key = "_pos_store_title"
) AS StoreTitle,
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos") AS POS,
(
  SELECT
    post_status
  FROM
    `wp_posts`
  WHERE
    `ID` = `i`.`order_id`
) AS
STATUS
FROM
  wp_woocommerce_order_items AS i
WHERE
  i.order_item_type = "line_item"
GROUP BY
  OrderID
ORDER BY
  OrderID DESC
) AS tPaymentMethods
WHERE StoreID = '.$store_id.' AND
		date(Date) = curdate() AND
		ProductID != "20548" AND
		ProductID != "20543" AND
		Status = "wc-completed" AND
		POS = "1" AND
        PaymentMethod != "cod" AND
	  PaymentMethod != "cheque"
GROUP BY
  StoreID,
  PaymentMethod');
	$auszahlungen = $wpdb->get_results('SELECT StoreID, StoreTitle, SUM(LineTotal) as TotalSum FROM (
SELECT `i`.`order_id` AS `OrderID`,
       `i`.`order_item_id` AS `OrderItemID`,
       @productID := (SELECT MAX(
                CASE 
                    WHEN `meta_key` = "_product_id"
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND `meta_key` = "_product_id") AS ProductID,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_line_total"
                       THEN `meta_value` 
                       ELSE NULL 
                   END) 
          FROM `wp_woocommerce_order_itemmeta`
         WHERE `order_item_id` = `i`.`order_item_id`
           AND `meta_key` = "_line_total") AS LineTotal,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_completed_date"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_completed_date") AS Date,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos") AS POS,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store") AS StoreID,
    
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store_title" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store_title") AS StoreTitle,
    
       (SELECT post_status
          FROM `wp_posts`
         WHERE `ID` = `i`.`order_id`) AS Status

 FROM `wp_woocommerce_order_items` AS `i` 
  WHERE `i`.`order_item_type` = "line_item"
GROUP BY OrderItemID
ORDER BY OrderID DESC) as Auszahlungen
WHERE StoreID = '.$store_id.' AND
	  date(Date) = curdate() AND
      ProductID = "20548" AND
      Status = "wc-completed" AND
	  POS = "1"
GROUP BY StoreID');
	$gutscheine = $wpdb->get_results('SELECT StoreID, StoreTitle, SUM(LineTotal) as TotalSum FROM (
SELECT `i`.`order_id` AS `OrderID`,
       `i`.`order_item_id` AS `OrderItemID`,
       @productID := (SELECT MAX(
                CASE 
                    WHEN `meta_key` = "_product_id"
                    THEN `meta_value` 
                    ELSE NULL 
                END)
           FROM `wp_woocommerce_order_itemmeta`
          WHERE `order_item_id` = `i`.`order_item_id`
            AND `meta_key` = "_product_id") AS ProductID,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_line_total"
                       THEN `meta_value` 
                       ELSE NULL 
                   END) 
          FROM `wp_woocommerce_order_itemmeta`
         WHERE `order_item_id` = `i`.`order_item_id`
           AND `meta_key` = "_line_total") AS LineTotal,

       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_completed_date"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_completed_date") AS Date,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos"
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos") AS POS,
 
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store") AS StoreID,
    
       (SELECT MAX(
                   CASE 
                       WHEN `meta_key` = "_pos_store_title" 
                       THEN `meta_value` 
                   ELSE NULL 
               END)
          FROM `wp_postmeta`
         WHERE `post_id` = `i`.`order_id`
           AND `meta_key` = "_pos_store_title") AS StoreTitle,
    
       (SELECT post_status
          FROM `wp_posts`
         WHERE `ID` = `i`.`order_id`) AS Status

 FROM `wp_woocommerce_order_items` AS `i` 
  WHERE `i`.`order_item_type` = "line_item"
GROUP BY OrderItemID
ORDER BY OrderID DESC) as Auszahlungen
WHERE StoreID = '.$store_id.' AND
	  date(Date) = curdate() AND
      ProductID = "20543" AND
      Status = "wc-completed" AND
	  POS = "1"
GROUP BY StoreID');
#2

I believe it is all of those Select MAX statements. Maybe you can create a subquery which captures the applicable meta_key fields first and then perform a single Select MAX to get the productID.

#3

Thanks! Can you or someone else help me with that?

#4

UPDATE: changed everything from database request to WooCommerce Querys and it’s performing much better now.