Mysql Optimization suggestion for large table

123
December 26, 2017, at 7:03 PM

i want to optimize this query,

    select
    location_id,
    dept_id,
    round(sum(sales),0),
    sum(qty),
    count(distinct tran_id),
    now()
    from tran_sales where tran_date<='2016-12-24'
    group by location_id,dept_id;

currently this query runs for around average of 98 seconds (Query took 97.4096 seconds.) in windows 10, 64 Bit OS, 16 GB RAM.

this is the table detail for your reference.

    CREATE TABLE tran_sales (
    tran_date date NOT NULL,
    location_id int(11) NOT NULL,
    dept_id int(11) NOT NULL,
    item_id varchar(25) NOT NULL,
    tran_id int(11) NOT NULL,
    sales float DEFAULT NULL,
    qty int(11) DEFAULT NULL,
    update_datetime datetime NOT NULL,
    PRIMARY KEY (tran_date,location_id,dept_id,item_id,tran_id),
    KEY tran_date (tran_date)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

the record count in table tran_sales: 13.5 Million.

Note: Even i tried without and with this index KEY tran_date (tran_date) . and average time it takes is 98 seconds with and without KEY tran_date (tran_date)

please suggest how to speedup the results by either changing the query or by changing some default settings of my.ini if that helps. Thanks.

Update the min date in table is : 2016-07-01, and the max date in table is : 2017-07-25

Answer 1

For this query:

select location_id, dept_id,
       round(sum(sales), 0), sum(qty), count(distinct tran_id),
       now()
from tran_sales
where tran_date <= '2016-12-24'
group by location_id, dept_id;

There is not much you can do. One attempt would be a covering index: (tran_date, location_id, dept_id, sales, qty), but I don't think that will help much.

READ ALSO
Script for tacking mysql backup in ubuntu 16

Script for tacking mysql backup in ubuntu 16

Plz help me to write the script in ubuntu for tacking mysql backup,I have already written few line in the scriptsh file for tacking mysql dump and run this script from crontab, but it is not working

215
Google CHARTS Display 0 values. (MySQL data with datetime)

Google CHARTS Display 0 values. (MySQL data with datetime)

I have been struggling with google charts with MySQL data

282
SQL Query to select data based on year and month

SQL Query to select data based on year and month

I'm writing an ecommerce app and im writing a script to track the sales of items according to year/month

128
Mysql dynamic variables against column name

Mysql dynamic variables against column name

How can I generate a dynamic variable with a column value? I got "S1" as value of a column, and I have defined a variable already as @S1, How can I access that variable using the current value of columnn

158