CODEWITHSUNDEEP
mysqlsql
davidb
davidb

Reputation: 273

Mysql Query Optimization suggesion

i want to optimize this query to speedup the results. currently this query runs for around 4 to 5 minutes in windows 7 32 Bit OS, 4 GB RAM.

record count in table transaction_sales: 400,000 record count in product_category: 200,000 this is my query 

        SELECT c.category_name,
    ROUND(sum(CASE WHEN t.business_date = '2017-12-24' THEN t.sales ELSE 0 END),0) today_sales,
    sum(CASE WHEN t.business_date = '2017-12-24' THEN t.qty ELSE 0 END) today_qty,
    COUNT(DISTINCT(CASE WHEN t.business_date = '2017-12-24' THEN t.tran_id END )) AS today_transactions,
    round(sum(t.sales),0)tilldate_sales,
    sum(t.qty)tilldate_qty,
    count(distinct t.tran_id)tilldate_transactions
    FROM transaction_sales t join product_category c on c.product_code=t.product_code
    group by c.category_name;

these are my tables with index details,

        CREATE TABLE product_category (
    product_code varchar(25) NOT NULL,
    category_name varchar(30) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE transaction_sales (
    id int(11) NOT NULL,
    business_unit_id int(11) NOT NULL,
    business_date date NOT NULL,
    tran_id varchar(10) NOT NULL,
    product_code varchar(25) NOT NULL,
    sales float NOT NULL,
    qty int(11) NOT NULL,
    last_update datetime NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ALTER TABLE product_category
    ADD PRIMARY KEY (product_code);

    ALTER TABLE transaction_sales
    ADD PRIMARY KEY (id),
    ADD UNIQUE KEY business_unit_id (business_unit_id,business_date,tran_id,product_code),
    ADD KEY business_date (business_date),
    ADD KEY product_code (product_code);

    ALTER TABLE transaction_sales
    MODIFY id int(11) NOT NULL AUTO_INCREMENT;

please suggest how to speedup the results.

Upvotes: 2

Views: 68

Answers (1)

Nick
Nick

Reputation: 5200

The CASE conditions are going to be a major factor slowing it down - three evaluations per record. If you can manage by running two queries instead of one: one to give you the figures for the particular day (the first group in the original query):

 SELECT c.category_name,
    ROUND(sum(t.sales)) today_sales,
    sum(t.qty) today_qty,
    COUNT(DISTINCT(t.tran_id)) AS today_transactions
    FROM transaction_sales t join product_category c on c.product_code=t.product_code WHERE t.business_date = '2017-12-24'
    group by c.category_name;

And a second to give you the figures for the whole table (the second group of three fields in your original query):

 SELECT c.category_name,
    ROUND(sum(t.sales)) today_sales,
    sum(t.qty) today_qty,
    COUNT(DISTINCT(t.tran_id)) AS today_transactions
    FROM transaction_sales t join product_category c on c.product_code=t.product_code
    group by c.category_name;

This should speed things up considerably. I've checked that the queries are syntactically correct on a test build using your table definitions, but obviously no data - although it does run pretty quickly on empty datasets!!

Upvotes: 3

Related Questions