SQL query running extremely slow

Question

I have an sql query that collects the year to date totals for all products in the inventory. This query runs quickly, returning around 5000 results in a little over a second.

The query used is

SELECT `ITINCODE` as Code, `IT_product_id` as ID,
SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = "O",`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL'
FROM `FITEMS`
WHERE (ITTYPE = 'O' OR `ITTYPE` = 'R') AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
GROUP BY `ITINCODE`
ORDER BY YTD_VAL DESC

I want to take the values in YTD_VAL and store them in the actual products table so that they can be used as an ORDER BY on other queries. I added a new field called ytd_val to the products table and then ran

UPDATE products p SET p.ytd_val = 
(SELECT SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = 'O',`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL'
FROM `FITEMS`
WHERE ITINCODE  = p.products_model AND (ITTYPE = 'O' OR `ITTYPE` = 'R') AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
GROUP BY `ITINCODE`
ORDER BY YTD_VAL DESC)

The idea was to run this by cron job each night so that the values were updated to reflect the previous days sales.

However, running this query has taken 10 plus minutes and it still hasn't completed.

ITINCODE in FITEMS table is the same as products_model in the products table. IT_product_id in the FITEMS table is the same as products_id in the products table.

What can I do to speed up the query? I thought that as the original results query returns quickly enough that simply updating the values on another table would take seconds longer!

Table structure is as follows:

show create table fitems\G;

Create Table: CREATE TABLE `fitems` (
  `ITUNIQUEREF` int(11) unsigned NOT NULL,
  `ITAMNT` int(11) NOT NULL,
  `ITREF` int(11) unsigned NOT NULL,
  `ITTYPE` char(1) NOT NULL,
  `ITVAL` decimal(10,4) NOT NULL,
  `ITVAT` decimal(10,4) NOT NULL,
  `ITPRICE` decimal(10,4) NOT NULL,
  `ITDATE` date NOT NULL,
  `ITBACKORDER` char(1) NOT NULL,
  `ITDISC` decimal(10,2) NOT NULL,
  `ITORDERREF` int(11) NOT NULL,
  `ITTREF` int(11) unsigned NOT NULL,
  `ITDATEDLY` date NOT NULL,
  `ITINCODE` char(20) NOT NULL,
  `IT_product_id` int(11) unsigned NOT NULL,
  `ITBUILT` int(11) NOT NULL,
  PRIMARY KEY (`ITUNIQUEREF`),
  KEY `ITREF` (`ITREF`,`ITTYPE`,`ITDATE`,`ITBACKORDER`,`ITORDERREF`,`ITDATEDLY`,`ITINCODE`,`IT_product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

.

show create table products\G;

CREATE TABLE `products` (
  `products_id` int(11) NOT NULL,
  `products_type` int(11) NOT NULL DEFAULT '1',
  `products_quantity` float NOT NULL DEFAULT '0',
  `products_model` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `products_image` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_group_a_price` decimal(15,4) NOT NULL,
  `products_group_b_price` decimal(15,4) NOT NULL,
  `products_group_c_price` decimal(15,4) NOT NULL,
  `products_group_d_price` decimal(15,4) NOT NULL,
  `products_group_e_price` decimal(15,4) NOT NULL,
  `products_group_f_price` decimal(15,4) NOT NULL,
  `products_group_g_price` decimal(15,4) NOT NULL,
  `products_virtual` tinyint(1) NOT NULL DEFAULT '0',
  `products_date_added` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` float NOT NULL DEFAULT '0',
  `products_status` tinyint(1) NOT NULL DEFAULT '0',
  `products_tax_class_id` int(11) NOT NULL DEFAULT '0',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` float NOT NULL DEFAULT '0',
  `products_quantity_order_min` float NOT NULL DEFAULT '1',
  `products_quantity_order_units` float NOT NULL DEFAULT '1',
  `products_priced_by_attribute` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_free` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_call` tinyint(1) NOT NULL DEFAULT '0',
  `products_quantity_mixed` tinyint(1) NOT NULL DEFAULT '0',
  `product_is_always_free_shipping` tinyint(1) NOT NULL DEFAULT '0',
  `products_qty_box_status` tinyint(1) NOT NULL DEFAULT '1',
  `products_quantity_order_max` float NOT NULL DEFAULT '0',
  `products_sort_order` int(11) NOT NULL DEFAULT '0',
  `products_canonical` text COLLATE utf8_unicode_ci NOT NULL,
  `products_discount_type` tinyint(1) NOT NULL DEFAULT '0',
  `products_discount_type_from` tinyint(1) NOT NULL DEFAULT '0',
  `products_price_sorter` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `master_categories_id` int(11) NOT NULL DEFAULT '0',
  `products_mixed_discount_quantity` tinyint(1) NOT NULL DEFAULT '1',
  `metatags_title_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_products_name_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_model_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_price_status` tinyint(1) NOT NULL DEFAULT '0',
  `metatags_title_tagline_status` tinyint(1) NOT NULL DEFAULT '0',
  `pricing_group` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ytd_val` int(20) NOT NULL,
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added_zen` (`products_date_added`),
  KEY `idx_products_status_zen` (`products_status`),
  KEY `idx_products_date_available_zen` (`products_date_available`),
  KEY `idx_products_ordered_zen` (`products_ordered`),
  KEY `idx_products_model_zen` (`products_model`),
  KEY `idx_products_price_sorter_zen` (`products_price_sorter`),
  KEY `idx_master_categories_id_zen` (`master_categories_id`),
  KEY `idx_products_sort_order_zen` (`products_sort_order`),
  KEY `idx_manufacturers_id_zen` (`manufacturers_id`),
  KEY `products_price` (`products_price`),
  KEY `products_status_products_price` (`products_status`,`products_price`),
  FULLTEXT KEY `idx_enhanced_products_model` (`products_model`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

SELECT is always way faster then UPDATE.

To speed up an update:

• Look at the indexes on the table you are updating: Are they all needed? If not, remove unneeded ones (I would at least remove idx_products_status_zen since that is also covered by products_status_products_price)

• Look at the data model: Can you partition the table you are updating? If so, that will speed up the update since the indexes you are updating will be smaller and thus quicker to update;

• Use InnoDB. It is faster;

• Do you need to be ACID compliant? If not, then change the settings of InnoDB to speed up the system.

• If you are really using MySQL: Switch to MariaDB: It is about 8% faster;

• Install monitoring to see where your bottleneck is: IO or CPU: If it is IO on read, then try compression.

Answer 2

If you are satisfied with first query performance.

You can just transform your second query to use INNER JOIN like:

http://sqlfiddle.com/#!9/1028a/1

UPDATE products p 
INNER JOIN (
  SELECT SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = 'O',`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL',
  ITINCODE
FROM `FITEMS`
WHERE (ITTYPE = 'O' OR `ITTYPE` = 'R') 
  AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
GROUP BY `ITINCODE`
  ) t
ON t.ITINCODE  = p.products_model
SET p.ytd_val = t.YTD_VAL

UPDATE And by the way you don't need that ORDER BY YTD_VAL DESC it has no sense in this particular case I guess.

UPDATE 2

UPDATE products p 
    INNER JOIN (
      SELECT SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = 'O',`ITVAL`, -`ITVAL` ), 0) ) as 'YTD_VAL',
      IT_product_id 
    FROM `FITEMS`
    WHERE (ITTYPE = 'O' OR `ITTYPE` = 'R') 
      AND ITBACKORDER = 'N' AND ITAMNT > 0 AND YEAR(`ITDATE`) >= YEAR(CURDATE() ) -1
    GROUP BY `IT_product_id`
      ) t
    ON t.IT_product_id = p.products_id 
    SET p.ytd_val = t.YTD_VAL

Answer 3

I am not one to say do it, but consider the case for Covering Indexes. One in particular on table fitems. Those columns are relatively slim for that query. I will spook up one to try on particular columns. We have seen cases where terribly long queries can be completed in snappy time. But no promises. Ah, I see you have some. Was looking at the first edit with the alter tables below it. I will keep looking.

Covering Index

A covering index is one in which the query can be resolved via a quick stroll through the index b-tree, without requiring a lookup into the actual table data page. These are the ultimate nirvana for speed. Percona quick article on it.

Explain

And run the query (the update one) thru Explain and examine the output. See also the article Using Explain to Write Better Mysql Queries.

Note, some of these comments are for those that follows, not necessarily this op. He seems to have his house in order pretty well.

Answer 4

In your subquery, neither the order by nor the group by are necessary, so the update can be written as:

UPDATE products p
    SET p.ytd_val = (SELECT SUM(IF(YEAR(`ITDATE`) = YEAR(CURDATE() ), IF(ITTYPE = 'O',`ITVAL`, -`ITVAL` ), 0) )
                     FROM `FITEMS`
                     WHERE FITEMS.ITINCODE = p.products_model AND
                           ITTYPE IN ('O', 'R') AND
                           ITBACKORDER = 'N' AND
                           ITAMNT > 0 AND
                           YEAR(`ITDATE`) >= YEAR(CURDATE() ) - 1
                    );

For this, you want an index on FITEMS(ITINCODE, ITBACKORDER, ITTYPE, ITAMNT, ITVAL). This might significantly speed your query.