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??

    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??

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

        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.

          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.

            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

              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.

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

                  Write a Reply...