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:

1. Can you think of a solution for improving query performance (query below)

2. 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

3. 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)

Answer 1

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:

1. You don't use any columns from the table
2. 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:

1. 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.
2. 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).
3. 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.