Description:
Hi,
I have a table thats show max points and user id for specific year .Number of rows about 4 M.
CREATE TABLE `cms_persons_diary_2016` (
`survey_id` INT(11) NOT NULL DEFAULT '0',
`p_id` INT(11) NOT NULL DEFAULT '0',
`date` DATE NOT NULL,
`day_kavicent` DECIMAL(10,4) NULL DEFAULT NULL,
`post_date` DATETIME NULL DEFAULT NULL,
`month` VARCHAR(10) NOT NULL,
`weekyear` INT(11) NULL DEFAULT NULL,
`year` INT(11) NULL DEFAULT NULL,
INDEX `year` (`year`, `p_id`, `day_kavicent`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
when I run query I get a result I want but it runs about 3 sec
select sql_big_result q.p,max(q.w) as p from (SELECT distinct p_id p ,sum(`day_kavicent`) as w
FROM cms_persons_diary_2016 where year=2016 group by p_id
order by w desc)as q
Profile shows only copying to tmp table takes that 3 seconds
Temp table is optimized.I tried to do anything bu can not decrease it more than 3 sec.May be it is a bug of 5.5 version ? and it is impossible to do more
tmp_table_size=64M
max_heap_table_size=256M
explain
1 PRIMARY <derived2> ALL \N \N \N \N 46914
2 DERIVED cms_persons_diary_2016 ref year year 5 1904117 Using where; Using index; Using temporary; Using filesort
How to repeat:
CREATE TABLE `cms_persons_diary_2016` (
`survey_id` INT(11) NOT NULL DEFAULT '0',
`p_id` INT(11) NOT NULL DEFAULT '0',
`date` DATE NOT NULL,
`day_kavicent` DECIMAL(10,4) NULL DEFAULT NULL,
`post_date` DATETIME NULL DEFAULT NULL,
`month` VARCHAR(10) NOT NULL,
`weekyear` INT(11) NULL DEFAULT NULL,
`year` INT(11) NULL DEFAULT NULL,
INDEX `year` (`year`, `p_id`, `day_kavicent`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
select sql_big_result q.p,max(q.w) as p from (SELECT distinct p_id p ,sum(`day_kavicent`) as w
FROM cms_persons_diary_2016 where year=2016 group by p_id
order by w desc)as q
tmp_table_size=64M
max_heap_table_size=256M
