optimization of query in mysql

Question

In my production database performance issue.

My sql query is taking long time for executing so the how to optimize the following mysql query.

select sum(distributor_earning) as col_0_0_, sum(distributor_share) as col_1_0_ 
from my_transaction mytransac0_ 
cross join del_transaction deltran1_ 
cross join user user3_ 
where mytransac0_.del_transaction_id=deltran1_.id and deltran1_.agent_parent_id=user3_.id and deltran1_.trx_status=4 and user3_.username='xyz';

It gives following output

+-----------+-----------+
| col_0_0_  | col_1_0_  |
+-----------+-----------+
| 579239.27 | 630557.77 |
+-----------+-----------+

1 row in set (15.14 sec)

I have over 100k records.

IN where condition index is build in del_transaction_id

Guessing that "a record in lakhs" means that you have 100k records. Please don't use Indian words. — James Z
– James Z, Commented Feb 5, 2018 at 16:47

walter · Accepted Answer · 2018-02-05 11:02:42Z

2

Firstly, you should replace `WHERE` clause with `ON` clause.

select sum(distributor_earning) as col_0_0_, sum(distributor_share) as col_1_0_ 
from my_transaction mytransac0_ 
cross join del_transaction deltran1_ ON mytransac0_.del_transaction_id=deltran1_.id AND deltran1_.trx_status=4
cross join user user3_ ON deltran1_.agent_parent_id=user3_.id AND user3_.username='xyz';

For more details about the differences between WHERE and ON: MySQL join with where clause.

Secondly, you'd better create indexes on the columns involved in this query.

It will run much faster when you finish these two steps.

answered Feb 5, 2018 at 11:02

walter

1,2698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sumesh TG · Accepted Answer · 2018-02-05 10:54:38Z

1

Create views for improve perfomance

Refer Is a view faster than a simple query?

answered Feb 5, 2018 at 10:54

Sumesh TG

2,5732 gold badges17 silver badges29 bronze badges

Comments

aswzen · Accepted Answer · 2018-02-05 10:56:58Z

1

Have you ever try sql indexing? Try this query on your mysql

CREATE INDEX INDEX_FAST_2
ON user(id, username);

After succeeded then try to run your query again.

answered Feb 5, 2018 at 10:56

aswzen

1,66234 silver badges48 bronze badges

Collectives™ on Stack Overflow

optimization of query in mysql

3 Answers 3

Firstly, you should replace `WHERE` clause with `ON` clause.

Secondly, you'd better create indexes on the columns involved in this query.

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Firstly, you should replace WHERE clause with ON clause.

Secondly, you'd better create indexes on the columns involved in this query.

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Firstly, you should replace `WHERE` clause with `ON` clause.