expensive query takes down database server -- looking for ways to mitigate

Question

I have a very expensive query that creates a temporary table for reporting purposes. I am having a problem where a denial of service attack takes place where 50+ of these queries pile up (All from the same user). This causes the database server to pretty much be taken down. This query can take 1-10 seconds to run and can have a lot of rows in the resulting temporary table.

I am not sure if there is an easy way to improve the query performance without re-architecting the entire reporting piece of the application.

What are some solutions I can use to apply that will solve the problem with these queries piling up and taking down my server.

My questions are:

Can you think of a solution for improving query performance (query below)
Does it make sense to place a session limit of 1 for the query so it can only run once and prevent a pile up
Any other ideas on how to prevent this attack.

Here is the query:

CREATE TEMPORARY TABLE phppos_sales_items_temp 
(SELECT phppos_sales.deleted as deleted,phppos_sales.deleted_by as deleted_by, sale_time, date(sale_time) as sale_date, 
phppos_sales_items.sale_id, comment,payment_type, customer_id, employee_id, phppos_items.item_id, NULL as item_kit_id, 
supplier_id, quantity_purchased, item_cost_price, item_unit_price, category, discount_percent,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal, 
phppos_sales_items.line as line, serialnumber, phppos_sales_items.description as description,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax,
(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit 
FROM phppos_sales_items 
INNER JOIN phppos_sales ON phppos_sales_items.sale_id=phppos_sales.sale_id 
INNER JOIN phppos_items ON phppos_sales_items.item_id=phppos_items.item_id 
LEFT OUTER JOIN phppos_suppliers ON phppos_items.supplier_id=phppos_suppliers.person_id 
LEFT OUTER JOIN phppos_sales_items_taxes ON phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id and phppos_sales_items.item_id=phppos_sales_items_taxes.item_id and phppos_sales_items.line=phppos_sales_items_taxes.line 
WHERE sale_time BETWEEN "2014-04-01 00:00:00" and "2014-04-30 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0 
GROUP BY sale_id, item_id, line) 

UNION ALL 

(SELECT phppos_sales.deleted as deleted,phppos_sales.deleted_by as deleted_by, sale_time, date(sale_time) as sale_date, 
phppos_sales_item_kits.sale_id, comment,payment_type, customer_id, employee_id, NULL as item_id, phppos_item_kits.item_kit_id, '' as supplier_id, 
quantity_purchased, item_kit_cost_price, item_kit_unit_price, category, discount_percent, 
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) as subtotal, 
phppos_sales_item_kits.line as line, '' as serialnumber, phppos_sales_item_kits.description as description, 
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)+(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as total, 
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax, 
(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) - (item_kit_cost_price*quantity_purchased) as profit 
FROM phppos_sales_item_kits 
INNER JOIN phppos_sales ON phppos_sales_item_kits.sale_id=phppos_sales.sale_id 
INNER JOIN phppos_item_kits ON phppos_sales_item_kits.item_kit_id=phppos_item_kits.item_kit_id 
LEFT OUTER JOIN phppos_sales_item_kits_taxes ON phppos_sales_item_kits.sale_id=phppos_sales_item_kits_taxes.sale_id and phppos_sales_item_kits.item_kit_id=phppos_sales_item_kits_taxes.item_kit_id and phppos_sales_item_kits.line=phppos_sales_item_kits_taxes.line 
WHERE sale_time BETWEEN "2014-04-01 00:00:00" and "2014-04-30 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0 
GROUP BY sale_id, item_kit_id, line) 

ORDER BY sale_id, line

EXPLAIN:

+----------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
| id | select_type  | table                        | type   | possible_keys                    | key          | key_len | ref                                                                                                       | rows | Extra                                        |
+----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY      | phppos_sales                 | range  | PRIMARY,location_id,sales_search | sales_search | 12      | NULL                                                                                                      |  113 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | phppos_sales_items           | ref    | PRIMARY,item_id                  | PRIMARY      | 4       | pos.phppos_sales.sale_id                                                                                  |    1 |                                              |
|  1 | PRIMARY      | phppos_items                 | eq_ref | PRIMARY                          | PRIMARY      | 4       | pos.phppos_sales_items.item_id                                                                            |    1 |                                              |
|  1 | PRIMARY      | phppos_suppliers             | ref    | person_id                        | person_id    | 4       | pos.phppos_items.supplier_id                                                                              |    1 | Using index                                  |
|  1 | PRIMARY      | phppos_sales_items_taxes     | ref    | PRIMARY,item_id                  | PRIMARY      | 12      | pos.phppos_sales_items.sale_id,pos.phppos_sales_items.item_id,pos.phppos_sales_items.line                 |    1 |                                              |
|  2 | UNION        | phppos_sales_item_kits       | ALL    | PRIMARY,item_kit_id              | NULL         | NULL    | NULL                                                                                                      |    1 | Using temporary; Using filesort              |
|  2 | UNION        | phppos_item_kits             | eq_ref | PRIMARY                          | PRIMARY      | 4       | pos.phppos_sales_item_kits.item_kit_id                                                                    |    1 |                                              |
|  2 | UNION        | phppos_sales_item_kits_taxes | ref    | PRIMARY,item_id                  | PRIMARY      | 12      | pos.phppos_sales_item_kits.sale_id,pos.phppos_sales_item_kits.item_kit_id,pos.phppos_sales_item_kits.line |    1 |                                              |
|  2 | UNION        | phppos_sales                 | eq_ref | PRIMARY,location_id,sales_search | PRIMARY      | 4       | pos.phppos_sales_item_kits.sale_id                                                                        |    1 | Using where                                  |
| NULL | UNION RESULT | <union1,2>                   | ALL    | NULL                             | NULL         | NULL    | NULL                                                                                                      | NULL | Using filesort                               |
+----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+

10 rows in set (0.00 sec)

Create tables:

mysql> show create table phppos_sales_items;
| Table              | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales_items | CREATE TABLE `phppos_sales_items` (
  `sale_id` int(10) NOT NULL DEFAULT '0',
  `item_id` int(10) NOT NULL DEFAULT '0',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serialnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `quantity_purchased` decimal(23,10) NOT NULL DEFAULT '0.0000000000',
  `item_cost_price` decimal(23,10) NOT NULL,
  `item_unit_price` decimal(23,10) NOT NULL,
  `discount_percent` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `phppos_sales_items_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
  CONSTRAINT `phppos_sales_items_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales` (`sale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.01 sec)

mysql> show create table phppos_sales;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales | CREATE TABLE `phppos_sales` (
  `sale_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customer_id` int(10) DEFAULT NULL,
  `employee_id` int(10) NOT NULL DEFAULT '0',
  `comment` text COLLATE utf8_unicode_ci NOT NULL,
  `show_comment_on_receipt` int(1) NOT NULL DEFAULT '0',
  `sale_id` int(10) NOT NULL AUTO_INCREMENT,
  `payment_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cc_ref_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `auth_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `deleted_by` int(10) DEFAULT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `suspended` int(1) NOT NULL DEFAULT '0',
  `store_account_payment` int(1) NOT NULL DEFAULT '0',
  `location_id` int(11) NOT NULL,
  PRIMARY KEY (`sale_id`),
  KEY `customer_id` (`customer_id`),
  KEY `employee_id` (`employee_id`),
  KEY `deleted` (`deleted`),
  KEY `location_id` (`location_id`),
  KEY `phppos_sales_ibfk_4` (`deleted_by`),
  KEY `sales_search` (`location_id`,`store_account_payment`,`sale_time`,`sale_id`),
  CONSTRAINT `phppos_sales_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
  CONSTRAINT `phppos_sales_ibfk_4` FOREIGN KEY (`deleted_by`) REFERENCES `phppos_employees` (`person_id`),
  CONSTRAINT `phppos_sales_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `phppos_employees` (`person_id`),
  CONSTRAINT `phppos_sales_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `phppos_customers` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)

mysql> show create table phppos_items;
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tax_included` int(1) NOT NULL DEFAULT '0',
  `cost_price` decimal(23,10) NOT NULL,
  `unit_price` decimal(23,10) NOT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `allow_alt_description` tinyint(1) NOT NULL,
  `is_serialized` tinyint(1) NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  `is_service` int(1) NOT NULL DEFAULT '0',
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_number` (`item_number`),
  UNIQUE KEY `product_id` (`product_id`),
  KEY `phppos_items_ibfk_1` (`supplier_id`),
  KEY `name` (`name`),
  KEY `category` (`category`),
  KEY `deleted` (`deleted`),
  KEY `phppos_items_ibfk_2` (`image_id`),
  CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
  CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)

mysql> show create table phppos_suppliers;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_suppliers | CREATE TABLE `phppos_suppliers` (
  `person_id` int(10) NOT NULL,
  `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `account_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `account_number` (`account_number`),
  KEY `person_id` (`person_id`),
  KEY `deleted` (`deleted`),
  CONSTRAINT `phppos_suppliers_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `phppos_people` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)

mysql> show create table phppos_sales_items_taxes;
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_sales_items_taxes | CREATE TABLE `phppos_sales_items_taxes` (
  `sale_id` int(10) NOT NULL,
  `item_id` int(10) NOT NULL,
  `line` int(3) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `percent` decimal(15,3) NOT NULL,
  `cumulative` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`sale_id`,`item_id`,`line`,`name`,`percent`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `phppos_sales_items_taxes_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `phppos_sales_items` (`sale_id`),
  CONSTRAINT `phppos_sales_items_taxes_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
1 row in set (0.00 sec)

have you already looked at caching the result set? and indexes for your joins/conditions? — attila, Apr 20 '14 at 03:23
I have foreign key constraints. Could you give me an example of how I would create indexes for joins? — Chris Muench, Apr 20 '14 at 03:24
Well, my experience is a bit limited with MySQL, but it appears foreign key constraints only create indexes on innodb tables. ref - http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically . maybe you can provide the execution plan. I cannot provide guidance on indexes without knowing your table schema, size, update frequency, and more. — attila, Apr 20 '14 at 03:31
Asking how to improve a query given only that horrendous block of Perl code is hopeless. Give nicely formatted, typical query SQL along with your schema and you might get a useful answer. — Gene, Apr 20 '14 at 03:32
IRL @ChrisMuench create a table with the sales info in it when the sale comes out. — danny117, Apr 20 '14 at 03:34
Paste the output of `EXPLAIN your-select-statement` into the question, please. — Mike Sherrill 'Cat Recall', Apr 20 '14 at 04:01
I want to make sure results show up sale id then line ordering. How does it not get used? — Chris Muench, Apr 20 '14 at 05:45
@ChrisMuench - Because you're dumping it into a table. Tables aren't actually supposed to have any order - things like clustered indices are just implementation details. I'm not sure if optimizers can use `ORDER BY`s matching a _destination table_ clustered index - and that would be problematic in the case of insert triggers. Also, don't use `BETWEEN` with [date/time/timestamp types](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) - MySQL can specify how many fractional seconds! Or for anything else, really, for the same reason. — Clockwork-Muse, Apr 20 '14 at 08:03
Also, it appears that you can pre-aggregate the `SUM(CASE...)` parts in a table-reference subquery, which means you wouldn't have a `GROUP BY` over a monster statement (or the attendant possible data issues). The calculation of `subtotal` could be changed to `(item_unit_price * quantity_purchased * ((100 - discount_percent) / 100)`, which would be fewer (expensive) operations - you might have to check with accounting about that, though, they can be touchy about order-of-operations. The optimizer can _probably_ tell all the recalcs are the same thing.... — Clockwork-Muse, Apr 20 '14 at 08:21
@Clockwork-Muse Could you show an example of how I would do pre-aggregate SUM? I need the GROUP BY so I get rows of sale items, but if I can do a less expensive operation, that would be great. Also what are my other options instead of using BETWEEN? — Chris Muench, Apr 20 '14 at 13:25
As the blog entry says, use a lower-bound inclusive (`>=`), upper-bound exclusive (`<`) range query. In this case, it's even better because you can do stuff like `sale_time >= '2014-04-01' AND sale_time < '2014-01-01' + 1 MONTH`: instant semantic recognition, you're going for one month's worth of data, and it's safe regardless of the actual resolution of `sale_time`. Query tweaks coming up (requires answer). — Clockwork-Muse, Apr 20 '14 at 14:27

score 3 · Answer 1 · answered Apr 20 '14 at 16:24

Hmm, I might try writing your query along these lines:

SELECT Sale_Item.deleted, Sale_Item.deleted_by,
       Sale_Item.sale_time, Sale_Item.sale_date,
       Sale_Item.comment,
       Sale_Item.payment_type,
       Sale_Item.customer_id,
       Sale_Item.employee_id,
       Sale_Item.category,
       Sale_Item.sale_id, Sale_Item.item_id, NULL as item_kit_id, Sale_Item.line, 
       Sale_Item.supplier_id,
       Sale_Item.serialnumber, Sale_Item.description,
       Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
       Sale_Item.discount_percent,
       Sale_Item.lineSubtotal,
       Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) * COALESCE(Tax.cumulative, 0) AS lineTax,
       Sale_Item.lineSubtotal + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) * COALESCE(Tax.cumulative, 0)) AS lineTotal,
       Sale_Item.lineSubtotal - (Sale_Item.item_cost_price * Sale_Item.quantity_purchased) AS profit

FROM (SELECT Sale.deleted, Sale.deleted_by,
             Sale.sale_time, DATE(Sale.sale_time) AS sale_date,
             Sale.comment,
             Sale.payment_type,
             Sale.customer_id,
             Sale.employee_id,
             Item.category,
             Sale_Item.sale_id, Sale_Item.item_id, NULL as item_kit_id, Sale_Item.line, 
             Sale_Item.supplier_id,
             Sale_Item.serialnumber, Sale_Item.description,
             Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
             Sale_Item.discount_percent,
             (Sale_Item.item_unit_price * Sale_Item.quantity_purchased) - (Sale_Item.item_unit_price * Sale_Item.quantity_purchased * Sale_Item.discount_percent / 100) as lineSubtotal                 
      FROM phppos_sales_items Sale_Item
      JOIN phppos_sales Sale
        ON Sale.sale_id = Sale_Item.sale_id
           AND Sale.sale_time >= TIMESTAMP('2014-04-01')
           AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
           AND Sale.location_id = 1
           AND Sale.store_account_payment = 0) Sale_Item

LEFT JOIN (SELECT Tax.sale_id, Tax.item_id, Tax.line,
                  SUM(CASE WHEN Tax.cumulative = 1 THEN Tax.percent ELSE 0 END) as cumulative,
                  SUM(CASE WHEN Tax.cumulative <> 1 THEN Tax.percent ELSE 0 END) as non_cumulative
           FROM phppos_sales_item_taxes Tax
           JOIN phppos_sales Sale
             ON Sale.sale_id = Tax.sale_id
                AND Sale.sale_time >= TIMESTAMP('2014-04-01')
                AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
                AND Sale.location_id = 1
                AND Sale.store_account_payment = 0
           GROUP BY Tax.sale_id, Tax.item_id, Tax.line) Tax
       ON Tax.sale_id = Sale_Item.sale_id
          AND Tax.item_id = Sale_Item.sale_id
          AND Tax.line =Sale_Item.line

Moved several columns for organizational purposes. This should have no large effect on processing time.

I removed the reference to phppos_suppliers as:

You don't use any columns from the table
It's a LEFT JOIN, meaning you don't require rows to exist there.

I moved the GROUP BY into a new subquery, because phppos_sales_item_taxes is the only table that could have duplicate rows for the given criteria. I included the reference to phppos_sales because I'm not sure if MySQL's optimizer (or any, really) is smart enough to push citeria down.

The main part of the query has been moved to a subquery simply so I wouldn't need to type the formula for lineSubtotal multiple times. I've used the same formulas throughout, but there are simplified versions available:

Sale_Item.item_unit_price * Sale_Item.quantity_purchased * (1 - (Sale_Item.discount_percent / 100)) as lineSubtotal  

Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative + Tax.cumulative + Tax.non_cumulative * Tax.cumulative, 0) as Tax

.... you may have to run these by accounting, though, as they tend to be (understandably) touchy about order of operations. This may result in a faster runtime but I doubt it; mostly this is about simplification of the terms to something more readable.

You didn't provide any table layouts for the other half of the query, but I presume it's similar. The related modification is left as an exercise for the reader.

General Mitigation Strategies

Beyond any potential speedup changing the query might have, there's a number of things you could do to curtail the problem:

In your application layer, force this query (and possibly others) to go through a job submission process whose results can be retrieved later. A new copy of this query can't be run until the previous one completes. I assume php has an existing library for this. Simply throttling submission in general may be all you need.
The data being retrieved appears amenable to caching - store everything prior to the most recent processed sale_date, and then only get new information on-the-fly (although the transformation isn't really that different from the original - however, simply not doing more joins may help).
Disallow queries over the current processing timespan. This should keep the system from attempting to access rows that haven't been committed yet, and potentially away from index pages under modification. This sort of trick works best if your storage is laid out to take advantage of concurrent I/O.

Thanks! I will give this a try tomorrow. – Chris Muench Apr 20 '14 at 21:11 — Chris Muench, Apr 20 '14 at 21:11

expensive query takes down database server -- looking for ways to mitigate

1 Answers1

General Mitigation Strategies

Linked