Optimize MySQL query performance

Question

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

your entry date is date time value why your doing sub string on it this will slow down the performance any reason ? — Mahesh Madushanka
– Mahesh Madushanka, Commented Jun 29, 2016 at 16:52
why substring(entry_date, 1, 11) but not just entry_date ? — Alex
– Alex, Commented Jun 29, 2016 at 16:53
You are correct; using any function on an indexed field in a condition or grouping results in ignoring the index (for that condition or grouping). Unfortunately, this will even include using CAST or other date functions you could use to get the date of the datetime field. — Uueerdo
– Uueerdo, Commented Jun 29, 2016 at 16:53
substring(entry_date, 1, 11) is getting the YYYY-MM-DD which ignore the HH:MM:SS part — Haifeng Zhang
– Haifeng Zhang, Commented Jun 29, 2016 at 16:54
Yep using any function on the indexed column results in ignorance of index. — Pirate X
– Pirate X, Commented Jun 29, 2016 at 16:55

Alex · Accepted Answer · 2016-06-29 16:57:46Z

2

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;

answered Jun 29, 2016 at 16:57

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Haifeng Zhang Over a year ago

thanks for your reply. I updated my question to make it more precise.

Alex Over a year ago

your update change nothing. any function called against every record breaks the performance and will not use any index

Haifeng Zhang Over a year ago

any suggestions about how can I solve such scenarios? much appreciated

Alex Over a year ago

that is why I ANSWERED did you try?

Pirate X Over a year ago

I was about to post similar answer. What alex suggested is correct actually. Make three different columns (as per your need), index them and then use them with where clause. MySQL doesn't support Function-based Indexes. (Use Oracle maybe ?)

|

Pirate X · Accepted Answer · 2016-06-29 17:01:05Z

1

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);

edited Jun 29, 2016 at 17:01

Pirate X

3,1156 gold badges40 silver badges64 bronze badges

answered Jun 29, 2016 at 16:58

Mahesh Madushanka

3,0032 gold badges17 silver badges29 bronze badges

1 Comment

Mahesh Madushanka Over a year ago

then it will utilize status index when it perform group by

Collectives™ on Stack Overflow

Optimize MySQL query performance

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related