Slow working on join query same table when large of dataset ( want to faster then )?

Question

This query is maintain below table

SELECT A.XXX as XXX,B.total_result as total_cost
FROM TABLE  as A
LEFT JOIN 
(
    SELECT XXX,sum(`click`)*price as total_result
    FROM TABLE
    WHERE main_id = '1'  AND (DATE(`click_time`) BETWEEN '2017-11-01' AND '2017-11-30' OR `click_time` IS NULL)
    GROUP BY XXX,price
) B ON B.`XXX` = A.`XXX`
where B.click IS NOT NULL   
GROUP BY A.XXX
LIMIT 0,20

Table

    k_id    main_id     XXX      click  price   click_time
        1     1         aaa         1   0.25    2017-11-05
        2     1         bbb         1   0.36    2017-11-05
        3     1         bbb         1   0.45    2017-11-05
        4     1         aaa         1   0.36    2017-11-05
        5     1         ccc         1   0.98    2017-11-05
        6     1         bbb         1   0.55    2017-11-05
        7     1         aaa         1   0.25    2017-11-05
        8     1         ccc         1   0.98    2017-11-05
        9     1         aaa         1   0.25    2017-11-05
        10    1         bbb         1   0.45    2017-11-05

I want to this query modify working this table of large dataset

Your query is not [**Sargable**](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable). So adding an index on `click_time` will not help you first make it sargable query then run `EXPLAIN` plan on your query — M Khalid Junaid, Nov 29 '17 at 10:57
I want to first group by XXX after then that XXX on group by price ... — Amit bhalani, Nov 29 '17 at 11:00
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 29 '17 at 11:11

score 1 · Answer 1 · answered Nov 29 '17 at 15:28

There are few things you can do to optimize this query.

On the query side:

Avoid ordering if possible - I can see you have a GROUP BY clause in the subquery, but no ORDER BY clause. If you do not really care about the order of the results in the subquery, add the following order clause to the end of the subquery, which will tell MySQL not to use the default order and improve performance.

ORDER BY NULL

====

Avoid calling functions on potentially indexed columns - as it won't allow MySQL to use the index on that column. The following condition:

DATE(`click_time`) BETWEEN '2017-11-01' AND '2017-11-30'

Can be modified to this one, to avoid using the DATE function on the indexed column and instead only use functions on constant values:

click_time >= DATE('2017-11-01') AND click_time < (DATE('2017-11-30') + INTERVAL 1 DAY)

====

Do not use OFFSET values in your query - Instead of LIMIT X,Y, you can use the alternative approach for faster pagination with offset in MySQL.

===

Use numeric values whenever appropriate - When comparing a numeric column to a string, you're forcing MySQL to cast the column's value for each row from a number to a string and only then perform the comparison. Therefore, in the condition main_id = '1', if main_id is a numeric column, the condition should be:

main_id = 1

Instead of:

main_id = '1'

===

Also, if you can provide the schema structure, it will be possible to recommend the appropriate indexes for this situation.

By the way, I got the recommendations from this online MySQL query optimizer, so feel free to just enter your query and schema there to get indexing recommendations as well (you didn't provide the schema structure, otherwise I would have provided the indexing recommendations as well).

Slow working on join query same table when large of dataset ( want to faster then )?

1 Answers1