How to incrice mysql query speed ?

Question

1.00/5 (1 vote)

See more:

HI MY THIS MYSQL QUERY WORKING SLOW PLEASE HELP TO MAKE IT FAST

SELECT l.*
FROM lr l
LEFT JOIN poddetail p ON l.LrNum = p.LrNum
LEFT JOIN servicebilldetail sd ON sd.Desciption=l.LrNum 
WHERE l.ComID=10 
  AND l.FInID='2021-22'
  AND NOT EXISTS (
    SELECT *
    FROM poddetail
    WHERE l.LrNum = p.LrNum
  )
  AND NOT EXISTS (
    SELECT *
    FROM servicebilldetail
    WHERE l.LrNum = sd.Desciption
  )
ORDER BY l.LRDate DESC
LIMIT 250

What I have tried:

select l.* From lr l left join poddetail p on l.LrNum = p.LrNum left join servicebilldetail sd on sd.Desciption=l.LrNum where l.ComID=10 and l.FInID='2021-22' and not exists (select * from poddetail where l.LrNum = p.LrNum ) and not exists (select * from servicebilldetail where l.LrNum = sd.Desciption) order by l.LRDate Desc Limit 250

Posted 21-Apr-21 7:13am

Asif 7969814

Updated 21-Apr-21 22:08pm

Dave Kreskowiak

v2

Add a Solution

Comments

Dave Kreskowiak 21-Apr-21 19:27pm

The first step is to rewrite your SQL so it's readable.

2 solutions

Solution 2

Impossible to answer without access to your database.

The one thing that does jump out is that you should remove the LEFT JOINs, since you're not selecting anything from the joined tables, and your WHERE clause explicitly excludes records where there is a matching record in those tables.

Also, don't use SELECT *; list the columns you actually want to use.

SQL

SELECT 
    ... COLUMN LIST ...
FROM 
    lr l
WHERE 
    l.ComID = 10 
AND 
    l.FInID = '2021-22'
AND 
    NOT EXISTS 
    (
        SELECT *
        FROM poddetail
        WHERE l.LrNum = p.LrNum
    )
AND 
    NOT EXISTS 
    (
        SELECT *
        FROM servicebilldetail
        WHERE l.LrNum = sd.Desciption
    )
ORDER BY 
    l.LRDate DESC
LIMIT 250

Posted 21-Apr-21 22:08pm

Richard Deeming

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)

SeanChupas · Accepted Answer · 2021-04-21T07:43:00

Solution 1

Make sure you have indexes on fields you are using in your WHERE clause as well as fields you are joining on and ordering on.

Then break it up into smaller pieces and see what specific part is making it slow.

Use a MySql tool to analyze the query for you and tell you where the slowness is.

Posted 21-Apr-21 7:43am

SeanChupas

Comments

Asif 7969814 21-Apr-21 13:48pm

whay i get System.TimeoutException: Timeout in IO operation in asp.net?

SeanChupas 21-Apr-21 14:12pm

Because it takes too long. You can increase the timeout but should also look at optimizing the sql.