Making sql query faster by using indexes

Question

I have the following query (simplified):

EXPLAIN (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 
FROM phppos_sales_items 
INNER JOIN phppos_sales FORCE INDEX (sales_search) 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-03-01 00:00:00" and "2014-03-31 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0)

+----+-------------+--------------------------+--------+-----------------+--------------+---------+-------------------------------------------------------------------------------------+-------+-------------+
| id | select_type | table                    | type   | possible_keys   | key          | key_len | ref                                                                                 | rows  | Extra       |
+----+-------------+--------------------------+--------+-----------------+--------------+---------+-------------------------------------------------------------------------------------+-------+-------------+
|  1 | SIMPLE      | phppos_sales             | range  | sales_search    | sales_search | 12      | NULL                                                                                | 13098 | Using where |
|  1 | SIMPLE      | phppos_sales_items       | ref    | PRIMARY,item_id | PRIMARY      | 4       | pos.phppos_sales.sale_id                                                            |     1 |             |
|  1 | SIMPLE      | phppos_items             | eq_ref | PRIMARY         | PRIMARY      | 4       | pos.phppos_sales_items.item_id                                                      |     1 |             |
|  1 | SIMPLE      | phppos_suppliers         | ref    | person_id       | person_id    | 4       | pos.phppos_items.supplier_id                                                        |     1 | Using index |
|  1 | SIMPLE      | phppos_sales_items_taxes | ref    | PRIMARY,item_id | PRIMARY      | 12      | pos.phppos_sales.sale_id,pos.phppos_sales_items.item_id,pos.phppos_sales_items.line |     1 | Using index |
+----+-------------+--------------------------+--------+-----------------+--------------+---------+-------------------------------------------------------------------------------------+-------+-------------+


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 `suspended` (`suspended`),
  KEY `sales_search` (`sale_time`,`location_id`,`store_account_payment`,`suspended`),
  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`)
) ENGINE=InnoDB AUTO_INCREMENT=109948 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

FULL QUERY (Not simplified):

EXPLAIN  (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 FORCE INDEX (sales_search) 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-03-01 00:00:00" and "2014-03-31 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 FORCE INDEX (sales_search) 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-03-01 00:00:00" and "2014-03-31 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0 GROUP BY sale_id, item_kit_id, line)

+----+--------------+------------------------------+--------+---------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+
| id | select_type  | table                        | type   | possible_keys       | key          | key_len | ref                                                                                                       | rows  | Extra                                        |
+----+--------------+------------------------------+--------+---------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+
|  1 | PRIMARY      | phppos_sales                 | range  | sales_search        | sales_search | 12      | NULL                                                                                                      | 13098 | 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.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                 | range  | sales_search        | sales_search | 12      | NULL                                                                                                      | 13098 | Using where; Using join buffer               |
| NULL | UNION RESULT | <union1,2>                   | ALL    | NULL                | NULL         | NULL    | NULL                                                                                                      |  NULL |                                              |
+----+--------------+------------------------------+--------+---------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+

EDIT: New explain from step C:

mysql> EXPLAIN (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 INNER JOIN phppos_sales_items 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 phppos_sales.sale_id between 103194 and 109743 and sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59" 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      INNER JOIN phppos_sales_item_kits 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 phppos_sales.sale_id between 103194 and 109743 AND  sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59" GROUP BY sale_id, item_kit_id, line);
+----+--------------+------------------------------+--------+---------------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+
| id | select_type  | table                        | type   | possible_keys       | key         | key_len | ref                                                                                                                               | rows  | Extra                                        |
+----+--------------+------------------------------+--------+---------------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+
|  1 | PRIMARY      | phppos_sales                 | range  | PRIMARY             | PRIMARY     | 4       | NULL                                                                                                                              | 12878 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | phppos_sales_items           | ref    | PRIMARY,item_id     | PRIMARY     | 4       | pos_compare.phppos_sales.sale_id                                                                                                  |     1 |                                              |
|  1 | PRIMARY      | phppos_items                 | eq_ref | PRIMARY             | PRIMARY     | 4       | pos_compare.phppos_sales_items.item_id                                                                                            |     1 |                                              |
|  1 | PRIMARY      | phppos_suppliers             | ref    | person_id           | person_id   | 4       | pos_compare.phppos_items.supplier_id                                                                                              |     1 | Using index                                  |
|  1 | PRIMARY      | phppos_sales_items_taxes     | ref    | PRIMARY,item_id     | PRIMARY     | 12      | pos_compare.phppos_sales.sale_id,pos_compare.phppos_sales_items.item_id,pos_compare.phppos_sales_items.line                       |     1 |                                              |
|  2 | UNION        | phppos_item_kits             | ALL    | PRIMARY             | NULL        | NULL    | NULL                                                                                                                              |     1 | Using temporary; Using filesort              |
|  2 | UNION        | phppos_sales_item_kits       | ref    | PRIMARY,item_kit_id | item_kit_id | 4       | pos_compare.phppos_item_kits.item_kit_id                                                                                          |     1 | Using where                                  |
|  2 | UNION        | phppos_sales_item_kits_taxes | ref    | PRIMARY,item_id     | PRIMARY     | 12      | pos_compare.phppos_sales_item_kits.sale_id,pos_compare.phppos_sales_item_kits.item_kit_id,pos_compare.phppos_sales_item_kits.line |     1 |                                              |
|  2 | UNION        | phppos_sales                 | eq_ref | PRIMARY             | PRIMARY     | 4       | pos_compare.phppos_sales_item_kits.sale_id                                                                                        |     1 | Using where                                  |
| NULL | UNION RESULT | <union1,2>                   | ALL    | NULL                | NULL        | NULL    | NULL                                                                                                                              |  NULL |                                              |
+----+--------------+------------------------------+--------+---------------------+-------------+---------+-----------------------------------------------------------------------------------------------------------------------------------+-------+----------------------------------------------+
10 rows in set (0.00 sec)

you need to show what indexes you have on those tables already and more importantly the query plan which shows which tables are not using their respective index(es) — user3248346
– user3248346, Commented Apr 2, 2014 at 8:51
@I.K. have put the explain + show create table. Is there anything else needed? — Chris Muench
– Chris Muench, Commented Apr 2, 2014 at 8:55
"Using index" would mean MySQL can retrieve all the information it needs from a single index, without having to read the actual rows. You don't have an index that covers all the columns you need from phppos_sales. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Apr 4, 2014 at 12:11
@ChrisMuench can you alias the table names, otherwise it is hard to know what columns come from which tables in order to be able to improve the query. — user275683
– user275683, Commented Apr 4, 2014 at 12:39
use-the-index-luke.com is a great introduction to how and when to use indexes. It's well worth your time to go through it. — Andy Lester
– Andy Lester, Commented Apr 5, 2014 at 18:54

Trafz · Accepted Answer · 2014-04-05 22:33:06Z

2

+250

The index phppos_sales does not work as you wish because you request an aggregate on sale_id. In order to optimize your query time here is my suggestion (sorry I can not test it since I do not have your data ):

A. Change your key sales_search to:

KEY `sales_search` (`location_id`,`store_account_payment`,`sale_time`,`sale_id`)

( equality conditions must precede the range condition .. )

B. Compute and save the result of this request

   SELECT MIN(sales_id) as min_id, MAX(sales_id) as max_id FROM phppos_sales
    WHERE sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59" and phppos_sales.location_id='1' and phppos_sales.store_account_payment=0

This should use the sales_search.

C. Run this request

(replace @min_id and @max_id with the values computed at step 2)

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 INNER JOIN phppos_sales_items 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_id between @min_id and @max_id  sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59" 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 
    INNER JOIN phppos_sales_item_kits 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_id between @min_id and @max_id AND  sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59"
GROUP BY sale_id, item_kit_id, line

Please let us know how it performs. The primary key should now be a good choice for the planner since it is sorted in a "group-by compliant" order, so the filesort should not be needed.

Ok, I suggest here a request which should be a "best-case" and will give us a target for optimisation. Can you show us the execution plan and the execution time for it?

(SELECT phppos_sales.sale_id, count(*) as nb_sales 
     FROM phppos_sales INNER JOIN phppos_sales_items 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_id between @min_id and @max_id  sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59" GROUP BY sale_id
    ) 
    UNION ALL 
    (
SELECT phppos_sales.sale_id,  count(*)
    FROM phppos_sales 
    INNER JOIN phppos_sales_item_kits 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_id between @min_id and @max_id AND  sale_time BETWEEN "2014-03-01 00:00:00" and "2014-03-31 23:59:59"
GROUP BY sale_id )

edited Apr 5, 2014 at 22:33

Trafz

6363 silver badges13 bronze badges

answered Apr 5, 2014 at 16:33

Olivier S

9415 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Chris Muench Over a year ago

The only concern I have about the min sale id and the max sale id is that the date can be altered after the sale is created so it might not fall in that range anymore. I think this could be a problem.

Olivier S Over a year ago

so you must use the first method, which is safe and a bit slower

Chris Muench Over a year ago

So I basically add the new key that you described. I am confused on what the 2 choices are as they are both labeled 1.

Olivier S Over a year ago

I removed the suggestion. Sorry for the 1 1 1, this is 1. 2. 3. in the editor, but when I post it makes 1. 1. 1.

Olivier S Over a year ago

With A. B. C. it's probably less confusing

|

Collectives™ on Stack Overflow

Making sql query faster by using indexes

1 Answer 1

17 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

17 Comments

Your Answer

Sign up or log in

Post as a guest

Related