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?
$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');