Better MySQL Query Performance

Question

I want the SUM QTY elements of the element to be grouped by month, the request takes too much time (15-20 seconds) to retrieve.

-Total lines: 1495873 -Set lines: 9 - 12

The relationship between the two tables ( invoice_headerand invoice_detail) (one to many) is that it invoice_headeris the invoice header with only the outcome. Which is related to invoice_detail using location identifier ( loc_id) and invoice number ( invo_no), since each place has its own serial number. Detailed invoice information contains information about each invoice.

Is there a better way to improve the performance of this request, here it is:

SELECT SUM(invoice_detail.qty) AS qty, Month(invoice_header.date) AS month
FROM invoice_detail
JOIN invoice_header ON invoice_detail.invo_no = invoice_header.invo_no
AND  invoice_detail.loc_id = invoice_header.loc_id
WHERE invoice_detail.item_id = {$itemId}
GROUP BY Month(invoice_header.date)
ORDER BY Month(invoice_header.date)

EXPLAIN:

Invoice_header table structure:

CREATE TABLE `invoice_header` (
 `invo_type` varchar(1) NOT NULL,
 `invo_no` int(20) NOT NULL AUTO_INCREMENT,
 `invo_code` varchar(50) NOT NULL,
 `date` date NOT NULL,
 `time` time NOT NULL,
 `cust_id` int(11) NOT NULL,
 `loc_id` int(3) NOT NULL,
 `cash_man_id` int(11) NOT NULL,
 `sales_man_id` int(11) NOT NULL,
 `ref_invo_no` int(20) NOT NULL,
 `total_amount` decimal(19,2) NOT NULL,
 `tax` decimal(19,2) NOT NULL,
 `discount_amount` decimal(19,2) NOT NULL,
 `net_value` decimal(19,2) NOT NULL,
 `split` decimal(19,2) NOT NULL,
 `qty` int(11) NOT NULL,
 `payment_type_id` varchar(20) NOT NULL,
 `comments` varchar(255) NOT NULL,
 PRIMARY KEY (`invo_no`,`loc_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20286 DEFAULT CHARSET=utf8

Invoice_detail table structure:

CREATE TABLE `invoice_detail` (
 `invo_no` int(11) NOT NULL,
 `loc_id` int(3) NOT NULL,
 `serial` int(11) NOT NULL,
 `item_id` varchar(11) NOT NULL,
 `size_id` int(5) NOT NULL,
 `qty` int(11) NOT NULL,
 `rtp` decimal(19,2) NOT NULL,
 `type` tinyint(1) NOT NULL,
 PRIMARY KEY (`invo_no`,`loc_id`,`serial`),
 KEY `item_id` (`item_id`),
 KEY `size_id` (`size_id`),
 KEY `invo_no` (`invo_no`),
 KEY `serial` (`serial`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

, invoice_header, - .

invo_no invoice_header.

Answer 2

SQL?

SELECT count(*)
FROM invoice_detail
WHERE invoice_detail.item_id = {$itemId}

SQL 15-20 , item_id inovice_detail.

invoice_header invo_no loc_id, invoice_header. invo_no, loc_id date, , .

Answer 3

: invo_no, loc_id, item_id, date.

, invoice_header. , ... invo_no, loc_id