Click here to Skip to main content
15,848,157 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
See more:
Dear All, I have bellow left join query and it takes more than 4 minutes to complete.
I am looking for a way to optimize the query, any ideas please? bellow is the details:
1st Query:
SQL
select * from viewm16journeysq v
9106 rows fetched in 0.3945s (0.5718s)
2nd Query:
SQL
select * from test2 where type not in (1,2,3,4)
9108 rows fetched in 0.1940s (0.0041s)

and bellow is the left join
SQL
select cast(`v`.`SDU Reference No` as signed) AS `SDU Reference No`,
t2.`DABAccount`,t2.`Sent to Bank`
from `viewm16journeysq` `v`
left join test2 t2 on v.`SDU Reference No` = t2.SDURN and
t2.type  <> 1 and t2.type <> 2 and t2.type <> 3 and t2.type <> 4


9106 rows fetched in 747.9791s (4.1869s)


the last left join takes approx more than 4 mins, I really dont understand where is the problem as 1st and 2nd query executes fast.
Posted
Comments
Varun Sareen 21-Feb-12 1:17am    
why are you using (t2.type <> 1 and t2.type <> 2 and t2.type <> 3 and t2.type <> 4) instead of t2.type not in (1,2,3,4)?

May be the cast function and the join is taking time, as in previous cases no function and no cases are there :)
Abdul Rahman Hamidy 21-Feb-12 1:41am    
thanks for your reply, regarding "in & and", I think "and" executes faster than "in" and I also used "in" but its the same. I removed the cast function but still the same.

1 solution

MySQL is slow when it comes to such JOINs. postgres would still cope with it, but when things become more complicated you'll have to switch to Microsoft SQL Server or Oracle.
Some things you can try:
- set an index on test2.type
- set an index on test2.SDURN
- set an index on viewm16journeysq.`SDU Reference No`
- remove the cast
- try a WHERE clause:
SQL
...left join test2 t2 on v.`SDU Reference No` = t2.SDURN 
WHERE t2.type not in (1,2,3,4)

- which values can test2.type have? Can you change the clause to test2.type>4?
- is viewm16journeysq a table or a view? In the latter case, change your query such that it uses the underlying tables instead of the view.
 
Share this answer
 
Comments
Abdul Rahman Hamidy 21-Feb-12 3:42am    
Thanks for you reply, I did follow the steps
1. I changed t2.type = 0
2. I added index in type & SDURN fields and the same I added index in SDU Ref No in the original table
but still problems seems the same
Abdul Rahman Hamidy 21-Feb-12 3:50am    
Thanks Bernhard. OMG what a big change, finally I made it
here is the result 9106 rows fetched in 0.6145s (0.5294s). My query is the same as my post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900