CODEWITHSUNDEEP
mysqlsqlindexingquery-performance
Haifeng Zhang
Haifeng Zhang

Reputation: 31885

Optimize MySQL query performance

My solution: With @Alex's help

I added 3 new columns to the table, name them year(YYYY), month(YYYY-MM) and day(YYYY-MM-DD) and add 3 indexes into the table:

alter table vaadin_table add index (year, status);
alter table vaadin_table add index (month, status);
alter table vaadin_table add index (day, status);

Now my queries are:

select year,status, count(1) from vaadin_table group by year, status;
select month,status, count(1) from vaadin_table group by month, status;
select day,status, count(1) from vaadin_table group by day, status;

I can get the result in 2 seconds! Thanks for all your help, really appreciated! It seems like Mysql doesnt support functions on the indexed columns which makes my original post queries did not work

Edit: Thanks for all replies.

To make my question more clear. I need to get the daily/monthly/yearly stats from the table.

Therefore I use below to group by daily/monthly/yearly data in order:

substring(entry_date,1, 11) ---> YYYY-MM-DD

substring(entry_date,1, 7) ---> YYYY-MM

substring(entry_date,1, 4) ---> YYYY

All those 3 columns makes my queries slow.

Original Question: I have a 2.7 million rows table. It contains 3 columns: name, status and entry_date(YYYY-MM-DD HH:MM:SS)

CREATE TABLE IF NOT EXISTS test_table 
(id integer not null auto_increment primary key, 
name char(20) not null, status char(20) not null, 
entry_date datetime default 0);

My purpose is to get the daily numbers of per status:

SELECT substring(entry_date, 1, 11), status, count(1) 
FROM test_table 
GROUP BY
substring(entry_date, 1, 11), status;

It works fine but takes about 10 seconds to return the result.

To optimize it, I add index to the table as:

ALTER table test_table ADD INDEX test_index(entry_date, status);

I read some similar questions on line, all suggest adding index based on the group by order. But it doesn't help for my case. Is it because I am using substring of entry_date?

Please help, thanks

Upvotes: 1

Views: 420

Answers (2)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

To optimize it, my suggestions are as below

change query

SELECT date(entry_date), status, count(1) 
FROM test_table 
GROUP BY
status,date(entry_date);

then create index in following column order 

ALTER table test_table ADD INDEX test_index( status,entry_date);

Upvotes: 1

Alex
Alex

Reputation: 17289

SELECT entry_date, status, count(1) 
FROM test_table 
GROUP BY
DATE(entry_date), status;

or even better add extra column with DATE type

ALTER TABLE test_table ADD COLUMN entry_date1 DATE;
UPDATE test_table  SET entry_date1=DATE(entry_date);

SELECT entry_date1, status, count(1) 
FROM test_table 
GROUP BY
entry_date1, status;

Upvotes: 2

Related Questions