Solved

Query runs too slowly

Posted on 2013-01-17
3
177 Views
Last Modified: 2013-01-21
Here's the query:

SELECT   *
      FROM NC2 a LEFT OUTER JOIN Actions b ON  a.UniqueID = b.DealerID
      WHERE  NOT EXISTS (
                     SELECT c.DealerID
                     FROM    Actions c
                     WHERE  c.DealerID = b.DealerID
                     AND       c.EventID = 1
       )

It works, but it can take over a minute to return records.  The 2 tables only have a few hundred records each.

What I need it to do is the following:

Select every record in NC2 UNLESS the value of UniqueID in NC2 matches up with a field called DealerID in a table called Actions., and then only if they match and the EventID is equal to 1.

Thanks
0
Comment
Question by:Rush_2112
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38788227
you need a index on Actions table, field DealerID
that should help alot.
after that, please show the explain plan of the query
0
 
LVL 1

Author Comment

by:Rush_2112
ID: 38793651
how do i do an explain plan?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38793676
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Creating and Managing Databases with phpMyAdmin in cPanel.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question