find missing records

mfacer

Hi guys,

I have two tables

metrics

metricID
processID

metrics_custom_report

metricID
processID
orgID

I need to get ALL the records which exist in the metrics table and DO NOT exist in the metrics_custom_report table.

I have this code, but it crashes the computer basically I think because it's returning too many rows???

SELECT m.metricID FROM metrics
LEFT OUTER JOIN metrics_custom_report mcr ON m.metricID = mcr.metricID
WHERE mcr.metricID IS NULL
AND mcr.orgID="orgname"
AND mcr.processID="2.01"
AND m.processID="2.01"

where am I going wrong??

mfacer

OK, I've tried this SQL

SELECT metricID FROM metrics
WHERE metricID NOT IN 
(SELECT metricID FROM metrics_custom_report WHERE processID='2.01' AND orgID='mydemo')
AND processID = '2.01'

which does work, but it takes about 9 seconds to load the page (this is offline where the page usually loads in 0.x seconds)

I try the queries seperately and the page loads instantely. eg doing

SELECT metricID FROM metrics_custom_report WHERE processID='2.01' AND orgID='mydemo'

and

SELECT metricID FROM metrics WHERE processID="2.01"

but when combined they take 10 times as long - why is this??

cahva

Have you set the keys correctly? If not, the query can take a long time.

LordShryku

If it's a matter of load time, and you want to spare yourself the subquery or join, do them separately, and do the search with an [man]array_diff[/man]. But like cahva said, your keys may need some rework to make the queries run smoother.

nemonoman

Try EXPLAIN SELECT metricID FROM metrics
WHERE metricID NOT IN
(SELECT metricID FROM metrics_custom_report WHERE processID='2.01' AND orgID='mydemo')
AND processID = '2.01'

From the look of it, your big query does two full table scans, builds a big temporary table, and and scans that.

NOT IN typically will cause a non-indexed search
I'll bet orgID isn't indexed

This join, by the way

LEFT OUTER JOIN metrics_custom_report mcr ON m.metricID = mcr.metricID
WHERE mcr.metricID IS NULL

looks to be a first-class backbreaker.

As a rule: Search FOR values, not around them.
If using a NOT IN, make the items explcit rather than using a dynamic subselect.

get the results from
SELECT metricID FROM metrics_custom_report WHERE processID='2.01' AND orgID='mydemo'

implode the results into a string

1,2,3,4 <<etc

then

WHERE NOT IN (1,2,3,4)

rather than WHERE NOT IN (SELECT metricID

that should work SIGNIFICANTLY faster.

Sxooter

The standard way to do this in mysql is to use a left join / where null thing.

select a.id from a left join b on a.id=b.id where b.id is null

will give you all the a.ids that don't have a match in b

nemonoman

That is the 'standard' answer. On a query like this it will be slow as molasses. That is what the poster asked - why so slow. Looking for nulls or in a subselect will always be slower than searching for NOT IN a static list of values.

Sxooter

Yeah, I read what he was asking for not what he was actually doing... back to the bit mines I guess.