I have a query like this constructed in Java. I am qurying a table called MetaTable which has columns as TABLE_NAME, START_INDEX, END_INDEX, LOOKUP_TABLENAME. I am basically doing this to get the list of tables within a specific time gap.
This causes my DB to hang. I have run longer and more complicated queries and dont know why this is causing it to crash.


String query = "select LOOKUP_TABLENAME from MetaTable where TABLE_NAME = '" + tableName + "' and
(
(START_INDEX >= sIndex and START_INDEX <= eIndex and (END_INDEX >= eIndex or END_INDEX = -1)) OR
(START_INDEX <= sIndex and END_INDEX >= sIndex and (END_INDEX <= eIndex or END_INDEX = -1)) OR
(START_INDEX <= sIndex and (END_INDEX >= eIndex or END_INDEX = -1)) OR
(START_INDEX >= sIndex and (END_INDEX <= eIndex or END_INDEX = -1))
)";

A couple of suggestions:

1) Try removing all WHERE conditions and then test each one by applying only one at a time.

2) Add some indexes, particularly on the START_INDEX and the END_INDEX columns. Create two indexes, not a composite...

JoeB