haifzhan haifzhan - 5 months ago 24
MySQL Question

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

Answer
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;
Comments