Query Help!!
I have a book inventory system where people can log in and look up books
by titles. The Informix table is structured in the following way
with text and file_no as the columns. Some sample data is also included:
TABLE xyz
text file_no
--------- ----
MURDER 67
MURDER 123
MURDER 145
CHILDRENS 12
ADVENTURE 14
MYSTERY 123
MYSTERY 14
MYSTERY 66
SPORTS 12
MYSTERY 145
. .
. .
. .
The table has about 130,000 rows of data in it and there are indexes
on the text and file_no fields.
If the user types in on an input screen : MURDER MYSTERY
I need to find the file_no number where the description is MURDER and MYSTERY
(the intersection of the two).
One solution is:
select file_no from xyz where text = "MURDER" and file_no in
(select file_no from xyz where text = "MYSTERY")
*This query is very very slow!!!!!
The other solution was:
select file_no from xyz
where text = "MURDER"
INTO TEMP T1;
select file_no from xyz
where text = "MYSTERY"
INTO TEMP T2;
select t1.file_no from t1,t2
where t1.file_no=t2.file_no;
*This is faster, but as fast as I would like to have it run.
Does anyone have a alternative solution to make these queries go faster?
Any help is appreciated.
Neal Leitner
AT&T Bell Labs
(201) 564-2463