CODEWITHSUNDEEP
mysqlperformance
HardCode
HardCode

Reputation: 1633

How can i optimize SUM() mysql Query

I have a mysql SUM query that runs on more than 0.6 million records. what i am currently doing is like this

 SELECT SUM (payment)
    FROM payment_table
    WHERE
       payment_date BETWEEN ... AND ...
       AND
       payment_status = 'paid'

I changed the query to this format to reduce the recordset but it is still taking almost same time. 

SELECT SUM(Payments)
FROM (
    SELECT payment AS Payments FROM payment_table WHERE
     payment_date BETWEEN DATE_FORMAT(NOW(), '2012-2-01') AND DATE_FORMAT(LAST_DAY(DATE_FORMAT(NOW(), '2012-2-01')), '%Y-%m-%d')
    AND
    payment_status = 'paid'
) AS tmp_table

Is their any way to optimize this sum query. EDIT:

This is the result when query is run with EXPLAIN

insert into ` (id,select_type,table,type,possible_keys, key,key_len,ref,rows,Extra`) values('1','SIMPLE','lps','index_merge','assigned_user_id,scheduled_payment_date,payment_status,deleted','deleted,assigned_user_id,payment_status','2,109,303',NULL,'23347','Using intersect(deleted,assigned_user_id,payment_status); Using where');

Upvotes: 0

Views: 2377

Answers (1)

Bohemian
Bohemian

Reputation: 425003

You should match the data type of the preducate with the column. Because payment_type is DATE, make the BETWEEN values DATE also:

 WHERE payment_date BETWEEN
     CURDATE() AND LAST_DAY(CURDATE())

Matching types ensures the index will be used.


In contrast, your query is using DATE_FORMAT(), which produces a text data type, so in order to perform the comparison, mysql is converting the payment_dare column to text, so it can't use the index (the index contains DATE values, not text values), so every single row is converted and compared.


If you are still having performance problems after making the change above, execute this:

ANALYZE TABLE payment_table;

Which will check the distribution of values in the indexed columns, which helps mysql make the right choice of index.

Upvotes: 1

Related Questions