0

This is a snippet of a view query for employee sales.

CREATE OR REPLACE VIEW EMPLOYEE
AS
SELECT
    customer.`Customer Name`,
    customer.cid,
    city_code.Dist_ID,
    terretory.Executive,
    terretory.Manager1,
    terretory.Manager2,
    district.Dist_Name,
    SUM(item.Sales_Amount) As Sales_Amount
FROM customer
INNER JOIN city_code
    ON city_code.City_id=customer.City_id
INNER JOIN district
    ON district.Dist_Id=city_code.Dist_ID
INNER JOIN terretory
    ON terretory.District_ID=district.Dist_Name
INNER JOIN sales_invoice
    ON sales_invoice.CID=customer.cid
INNER JOIN sales_item
    ON sales_item.Invoice_Number=sales_invoice.Invoice_Number
GROUP BY customer.`Customer Name`

The select operation is taking 2-3 minutes. How can the execution time be reduced? There are over 100,000 rows in the tables.

Here is the Explain query results enter image description here

Akshay
  • 700
  • 9
  • 23
  • 1
    The table `item` does not exist in your `SELECT` query. – Dykotomee Mar 10 '16 at 09:51
  • Make sure that all columns you are joining on is indexed. I think `Customer Name` also should be indexed. – Cyclonecode Mar 10 '16 at 09:53
  • Create Index on cid,customer_name,sales_invoice etc......| Flush Tables; Reset Query Cache; – Priyanshu Mar 10 '16 at 09:56
  • You are not selecting from each one of this tables, is it mandatory to join all of them? – sagi Mar 10 '16 at 09:56
  • Indexes will be your friend, but we need to see what you already have in place. Tell us what `EXPLAIN SELECT ...` returns. – Egg Mar 10 '16 at 09:57
  • @cyclone only Sales_Amount, Manager1 and Manager2 is not indexed rest of all are indexed. Should I index three of them also? – Akshay Mar 10 '16 at 09:58
  • @sagi No I am selecting at least one one field from the all tables which are joined. – Akshay Mar 10 '16 at 10:00
  • @Egg `Select` will returns the employee wise sale based on their districts. Here the main table is the sales_invoice from where i am getting the `customer id(cid)`, from there I am getting the customer `districts` suppose in territory table I have distributed the district per employee. Then i am calculating their sales from `sales_invoice` – Akshay Mar 10 '16 at 10:06
  • @AkshayRathod - He ask what `EXPLAIN SELECT ...` returns. Running it will show you what kind of query will get executed. – Cyclonecode Mar 10 '16 at 10:10
  • @AkshayRathod I mean, run your command with `EXPLAIN ` preceding it, [it will tell us](https://dev.mysql.com/doc/refman/5.7/en/using-explain.html) how the command is exectued, what columns are joined, what keys/indexes are used etc. – Egg Mar 10 '16 at 10:10
  • @egg i have added that screenshot of Explain query in my question. – Akshay Mar 10 '16 at 10:26
  • No index is used on sales_item table. Do you have an index on `sales_item.Invoice_Number` field? If not, create one. – Shadow Mar 10 '16 at 10:41
  • @Shadow yes i have already did that I have tried all all option which were suggested in column still it's taking 21sec (Min) to display the results. – Akshay Mar 10 '16 at 10:47
  • Well, the original time was 2-3 minutes, now it's down to 21 sec, that's an improvement. I would also change the group by clause to customer.cid (although this still would be against the sql standard) because that column is indexed, whereas the name is probably not. How many customers do you have? – Shadow Mar 10 '16 at 10:53
  • @Shadow I have approx 5000 customers and per customers may be there are 500-600 data in `sales_invoice` table. I tried group by `customer.cid` still same bro. – Akshay Mar 10 '16 at 10:58
  • In this case I would use limit and paging because it is unlikely that anyone would review 5000 records at a time. Can a customer have more than one city / territory / district? – Shadow Mar 10 '16 at 11:02
  • No one customer have single district allotted but employee may have more then 2 districts. – Akshay Mar 10 '16 at 11:06

0 Answers0