0

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

James Z
  • 12,209
  • 10
  • 24
  • 44
savan
  • 35
  • 5
  • Guessing that "a record in lakhs" means that you have 100k records. Please don't use Indian words. – James Z Feb 05 '18 at 16:47

3 Answers3

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.

walter
  • 1,199
  • 7
  • 13
1

Create views for improve perfomance

Refer Is a view faster than a simple query?

Sumesh TG
  • 2,557
  • 2
  • 15
  • 29
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.

aswzen
  • 1,512
  • 29
  • 46