DanL DanL - 6 months ago 33
SQL Question

Changing >= to <= causes query to exceed execution time limit

I have a query that checks inventory for

vehicles
that have not been seen in a week but that are not listed in the
sold_vehicles
table.

If I run this query:

SELECT all_vehicles.Vin
FROM all_vehicles
LEFT JOIN sold_vehicles
ON all_vehicles.Vin = sold_vehicles.Vin
WHERE
sold_vehicles.id IS NULL AND
all_vehicles.last_seen >= 1502672069
ORDER BY all_vehicles.id ASC
LIMIT 1000


Everything works fine.

However, if I reverse the
>=
to
<=
it exceeds the 120 second execution time limit. Why would reversing this behave this way? Any reason other than a lot of results to return, and wouldn't the limit fix that?

To be clear, this is nearly the exact same query, but with
<=
and a
LIMIT 10
yet fails to execute:

SELECT all_vehicles.Vin
FROM all_vehicles
LEFT JOIN sold_vehicles
ON all_vehicles.Vin = sold_vehicles.Vin
WHERE
sold_vehicles.id IS NULL AND
all_vehicles.last_seen <= 1502672069
ORDER BY all_vehicles.id ASC
LIMIT 10


Any ideas? Is it amount of results found alone? How do I fix it other than
LIMIT
?

Answer Source

Start by removing the order by. Does this return anything?

SELECT av.Vin
FROM all_vehicles av LEFT JOIN
     sold_vehicles sv
     ON av.Vin = sv.Vin
WHERE sv.id IS NULL AND
      av.last_seen <= 1502672069
--ORDER BY av.id ASC
LIMIT 10;

Then, I would create indexes on all_vehicles(last_seen, Vin, id) and sold_vehicles(Vin, id).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download