Icon View Incident Report

Serious Serious
Reported By: Herbert Sitz
Reported On: 8/11/2004
For: Version 4.10 Build 1
# 1832 Multiple SQL SELECT Join Conditions on the Same Tables Connected with an OR Result in Slow Results

I have a query that seemingly fails to make use of an available index in the join clause. The query looks like the following:

This query takes 3 or 4 seconds to return a small result set (2500 records) on base tables that themselves are very small (Contacts has 600 records, Addresses has around 700 records).

The field 'a.contactid' is indexed and queries with a single condition in the join clause execute quickly.

SELECT c.cid1, c.cid2, c.lastname, c.firstname
FROM Contacts c join Addresses a
ON ((c.cid1 = a.contactid) OR (c.cid2 = a.contactid))

Resolution Resolution
Fixed Problem on 9/20/2004 in version 4.11 build 1