Left join long time in mysql

Question

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 20-Feb-12 19:06pm

Abdul Rahman Hamidy

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Bernhard Hiller · Accepted Answer · 2012-02-20T21:06:00

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.