在查询中使用聚合函数需要花费大量时间

Table has 100 000 records, takes 20-21 seconds when use aggregate function. How to optimize this query?

SELECT source, sum(product_price*quantity) AS price 
FROM `sheet` 
WHERE source !='' 
GROUP BY source 
ORDER BY `price` DESC        

I have also used indexing in the table

ALTER TABLE `dbname`.`sheet` ADD INDEX `reprting_module` (`source`(30));  

This is the output after explain the query

First of all, you're asking your MySQL server to do some computation in this query, and then to sort the results. It will take some time. It necessarily must examine every, or almost every, row of your table. There's no magic to make those operations instantaneous.

Secondly, your WHERE source != '' filter may be defeating your indexing. You could try WHERE source > '' instead. That will allow MySQL's query planner to random-access your index, then scan it sequentially.

Third, your subset source index (source(30)) doesn't help performance.

Fourth, you can try creating a compound covering index on these columns:

    ALTER TABLE dbname.sheet 
      ADD INDEX `reprting_module` (source, product_price, quantity);

Then write your query like this:

SELECT source, SUM(product_price*quantity) AS price 
  FROM sheet 
 WHERE source > '' 
 GROUP BY source 
 ORDER BY SUM(product_price*quantity) DESC  

If you're lucky this will be a little faster. Why? Because MySQL can satisfy your entire query by random-accessing the index to the first non-empty source value, then sequentially scanning just the index to perform your computation.

Notice that the query I showed, with the index I showed, will be very fast indeed if you use

     WHERE source = 'some-particular-value'

to narrow down the scope of the computation.

"Prefix" indexes, such as INDEX(source(30)), are virtually useless. Please provide SHOW CREATE TABLE. If source could be VARCHAR(255) or smaller, simply add INDEX(source) But that is probably not useful here, since most of the table needs to be read.

How much RAM do you have? What is the value of innodb_buffer_pool_size? How big (GB) is the table? These combine to ask whether you are CPU-bound or I/O-bound, and whether a simple tuning fix can change it from I/O to CPU, thereby possibly speeding it up to 2 seconds. (20 seconds seems very high for a mere 100K rows.)