Icon View Incident Report

Serious Serious
Reported By: Danny Humphress
Reported On: 8/8/2008
For: Version 4.26 Build 3
# 2831 OR Operator Causes Un-Optimized Table Scans When One Condition Is Optimized and Other Is Not

have a table that contains two memo fields. One has a full text index and the other does not. The following query should be fast but it is quite slow:

I know that the LIKE operator won't be optimized but that part of the query should be partially optimized and, in fact, it does run quickly when I just use one of the WHERE conditions by itself. The query is slow only when the two are OR'd together. I could break this into two queries and UNION them, but this SQL is built on-the-fly based on a user query so there could be all kinds of combinations.

SELECT * 
FROM InvMarc
WHERE
((TEXTSEARCH('DISNEY WORLD' IN Keywords) AND
(UPPER(Contents) LIKE '%DISNEY WORLD%'))
OR
TEXTSEARCH('DISNEYLAND' IN Keywords))



Resolution Resolution
Fixed Problem on 8/11/2008 in version 4.27 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image