Query with multiple table joins taking too much time despite indexing

Question

Query-

SELECT SUM(sale_data.total_sale) as totalsale, `sale_data_temp`.`customer_type_cy` as `customer_type`, `distributor_list`.`customer_status` FROM `distributor_list` LEFT JOIN `sale_data` ON `sale_data`.`depo_code` = `distributor_list`.`depo_code` and `sale_data`.`customer_code` = `distributor_list`.`customer_code` LEFT JOIN `sale_data_temp` ON `distributor_list`.`address_coordinates` = `sale_data_temp`.`address_coordinates` LEFT JOIN `item_master` ON `sale_data`.`item_code` = `item_master`.`item_code` WHERE `invoice_date` BETWEEN "2017-04-01" and "2017-11-01" AND `item_master`.`id_category` = 1 GROUP BY `distributor_list`.`address_coordinates`

Query, rewritten with formatting.

SELECT SUM(sale_data.total_sale) as totalsale,
       sale_data_temp.customer_type_cy as customer_type,
       distributor_list.customer_status
  FROM distributor_list
  LEFT JOIN sale_data
             ON sale_data.depo_code = distributor_list.depo_code
            and sale_data.customer_code = distributor_list.customer_code
  LEFT JOIN sale_data_temp
            ON distributor_list.address_coordinates = sale_data_temp.address_coordinates
  LEFT JOIN item_master
           ON sale_data.item_code = item_master.item_code
 WHERE invoice_date BETWEEN "2017-04-01" and "2017-11-01"
   AND item_master.id_category = 1
 GROUP BY distributor_list.address_coordinates

DESC-

This Query is taking 7.5 seconds to run. My application contains 3-4 such queries. Therefore loading time appraches 1 min on server. My sale data table contains 450K records. Distributor list contains 970 records Item master contains 7774 records and sale_data_temp contains 324 records. I am using indexing but it is not being used for sale data table. All the 400K records are searched as is evident from explain sql. If I reduce the duration of BETWEEN clause than sale data table uses date index otherwise it scans all 400K rows. The rows between 01-04-2017 and 01-11-2017 are 84000 but still it scans 400K rows.

MYSQL EXPLAIN- MYSQL EXPLAIN Statement

I have modified queries two times with no success.

Modification 1:

SELECT SUM(sale_data.total_sale) as totalsale, `sale_data_temp`.`customer_type_cy` as `customer_type`, `distributor_list`.`customer_status` FROM `distributor_list` LEFT JOIN `sale_data` ON `sale_data`.`depo_code` = `distributor_list`.`depo_code` and `sale_data`.`customer_code` = `distributor_list`.`customer_code` AND `invoice_date` BETWEEN "2017-04-01" and "2017-11-01" LEFT JOIN `sale_data_temp` ON `distributor_list`.`address_coordinates` = `sale_data_temp`.`address_coordinates` LEFT JOIN `item_master` ON `sale_data`.`item_code` = `item_master`.`item_code` WHERE `item_master`.`id_category` = 1 GROUP BY `distributor_list`.`address_coordinates`

Modification 2

SELECT SQL_NO_CACHE SUM( sd.total_sale ) AS totalsale, `sale_data_temp`.`customer_type_cy` AS `customer_type` , `distributor_list`.`customer_status` FROM `distributor_list` LEFT JOIN (SELECT * FROM `sale_data` WHERE `invoice_date` BETWEEN "2017-04-01" AND "2017-11-01")sd ON `sd`.`depo_code` = `distributor_list`.`depo_code` AND `sd`.`customer_code` = `distributor_list`.`customer_code` LEFT JOIN `sale_data_temp` ON `distributor_list`.`address_coordinates` = `sale_data_temp`.`address_coordinates` LEFT JOIN `item_master` ON `sd`.`item_code` = `item_master`.`item_code` WHERE `item_master`.`id_category` =1 GROUP BY `distributor_list`.`address_coordinates`

HERE ARE MY INDEXES ON SALE DATA TABLE

`func` makes it sound like you have some inconsistencies in datatypes and/or collations. — Rick James, Nov 04 '17 at 20:42
Help us out -- format the two selects, and point at the differences. — Rick James, Nov 04 '17 at 20:43

ajmedway · Answer 1 · 2017-11-03T09:10:49.530

See the key column of the EXPLAIN results view - no key is being used at the moment so MySQL is not using any of your indexes for filtering out rows so it is scanning the whole table on each query. This is why it is taking so long.

I have taken a look at your first query with relation to your sale_data indices. It looks like you will need to create a new composite index on this table that contains the following columns only:

depo_code, customer_code, item_code, invoice_date, total_sale

I recommend that you name this index test1 and experiment with modifying the ordering of the columns and keep testing again each time using EXPLAIN EXTENDED until you achieve a selected key - you want to see index test1 has been selected in the key column.

See this answer that has helped me before with this, and it will help you understand the importance of correctly ordering your composite indices.

Looking at the cardinality of the single field indices, here is my best attempt at giving you the correct index to apply:

ALTER TABLE `sale_data` ADD INDEX `test1` (`item_code`, `customer_code`, `invoice_date`, `depo_code`, `total_sale`);

Good luck with your mission!

I tried making that multiple index. What it does is that it shows test1 and invoice date in possible keys but not uses it. — Gaurav Sharma, Nov 03 '17 at 05:24
Based on the `EXPLAIN`, `invoice_date` should be first in that "covering" index -- since it is the only thing in the `WHERE` clause. — Rick James, Nov 04 '17 at 20:46
Adding totalsale in covering index resolved the problem.The scanned rows were reduced from 400000 to 92000.But still it is taking the same time. — Gaurav Sharma, Nov 06 '17 at 05:08

score 0 · Answer 2 · answered Nov 02 '17 at 11:12

A few things to notice about your query.

You are misusing the notorious MySQL extension to GROUP BY. Read this, then mention the same columns in your GROUP BY clause as you mention in your SELECT clause.
Your LEFT JOIN sale_data and LEFT JOIN item_master operations are actually ordinary JOIN operations. Why? You mention columns from those tables in your WHERE clause.
Your best bet for speedup is doing a date-range scan on an index on sale_data.invoice_date. For some reason known only to the MySQL query planner's feverish machinations, you're not getting it.

Try refactoring your query. Here's one suggestion:

SELECT SUM(sale_data.total_sale) as totalsale,
       sale_data_temp.customer_type_cy as customer_type,
       distributor_list.customer_status
  FROM distributor_list
  JOIN sale_data
             ON sale_data.invoice_date BETWEEN "2017-04-01" and "2017-11-01"
            and sale_data.depo_code = distributor_list.depo_code
            and sale_data.customer_code = distributor_list.customer_code
  LEFT JOIN sale_data_temp
            ON distributor_list.address_coordinates = sale_data_temp.address_coordinates
  JOIN item_master
           ON sale_data.item_code = item_master.item_code
 WHERE item_master.id_category = 1
 GROUP BY sale_data_temp.customer_type_cy, distributor_list.customer_status

Try creating a covering index on sale_data for this query. You'll have to mess around a bit to get this right, but this is a starting point. (invoice_date, item_code, depo_code, customer_code, total_sale). The point of a covering index is to allow the query to be satisfied entirely from the index without having to refer back to the table's data. That's why I included total_sale in the index.

Please notice that index I suggested makes your index on invoice_date redundant. You can drop that index.

I used the index you mentioned including totalsale. The sale data rows came down to 92000 from 400000. But when i run Query in phpmyadmin,It is taking the same time of 7.5sec despite reduced sale data rows in explain SQL. — Gaurav Sharma, Nov 03 '17 at 06:25

Query with multiple table joins taking too much time despite indexing

2 Answers2