Remove using temporary and Filesort from query execution

Question

I have one table having almost 90 columns and also using Range Partition on it. This table has almost 50lacs records. When I used GROUP BY in Query at that time it shows "using temporary" and "filesort" in explain statement.

Query :

 SELECT
 subscribe_time * 0.000001 AS start_date,
 subscribe_time * 0.000001 AS end_date,
 (
    IFNULL(
      ROUND(
         SUM(
          CASE WHEN(
              subscribe_duration > 20000 AND subscribe_status = '1'
              ) THEN 1 ELSE 0
          END
          ) /(
            SUM(
              CASE WHEN(subscribe_status = '1') THEN 1 ELSE 0
              END
           )
      ) * 100,
    2
  ),
  0
)
) AS subscribe_avg

FROM
  tbl_subscription a
WHERE
  1 AND subscribe_time > 0 AND subscribe_time BETWEEN "1525113000000000" AND "1524680999999999"
GROUP BY
  EXTRACT(
    YEAR
    FROM
       FROM_UNIXTIME(subscribe_time * 0.000001)
    ),
  EXTRACT(
    MONTH
    FROM
    FROM_UNIXTIME(subscribe_time * 0.000001)
  ),
  EXTRACT(
     WEEK
     FROM
     FROM_UNIXTIME(subscribe_time * 0.000001)
 ),
 EXTRACT(
   DAY
   FROM
   FROM_UNIXTIME(subscribe_time * 0.000001)
),
sub_user,
subscribe_ip,
subscribe_zone,
subscribe_approval

Indexing is already given on field1. field1 is not our PK. On total 5 columns indexing are given.

When I write GROUP BY query took almost 43sec to execute. and When I remove GROUP BY at that time it is taking 0.27sec execution time.

Also when we write "ORDER BY NULL" at last then filesort is removed. But I don't want to use ORDER BY in my query. How to reduce query execution time?

Also, I tried to fetch data from a single partition also, but still query is taking the same time. Query to fetch data from single partition :

 SELECT field1, field2 FROM TABLE_NAME PARTITION(p1) WHERE 1 AND 
   field1='SOME_VALUE' GROUP BY field1;

Give some suggestions to reduce query execution time.

Answer 1

The first expression of the GROUP BY can probably be replaced by

subscribe_time

or perhaps

FLOOR(subscribe_time * 0.000001)

I see no need to break it apart just to do the GROUP BY.

Can you provide SHOW CREATE TABLE? - We need to see indexes, partitioning, and probably other details. In particular, does it have either or both of

PARTITION BY RANGE(subscribe_time)
INDEX(subscribe_time)