Query Help!! 
Author Message
 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
---------        ----
{*filter*}           67
{*filter*}           123
{*filter*}           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 : {*filter*} MYSTERY
I need to find the file_no number where the description is {*filter*} and MYSTERY
(the intersection of the two).

One solution is:

        select file_no from xyz where text = "{*filter*}" 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 = "{*filter*}"
        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



Sat, 15 Oct 1994 03:30:59 GMT
 Query Help!!

Quote:
neal.l.leitner writes:
>    select file_no from xyz
>    where text = "{*filter*}"
>    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.

Can you join it to itself, with something like this ?

select a.file_no from xyz a, xyz b
   where a.file_no = b.file_no
     and a.text = "{*filter*}"
     and b.text = "MYSTERY";

If you can, is that slower/faster ?   (sorry, I don't have 130,000
rows to play with at the moment  :-)

Or is the point that they can pick lots of keywords  (in which
case I probably blew it completely)  ?   If so, you might UNION
all the individual SELECTs into a temp table and then self-join
that.

Quote:
> Neal Leitner
> AT&T Bell Labs
> (201) 564-2463

Good luck ...

Cheers - Tony.

PS. It's bucketting down with rain.  Does anyone have a spare warm-climate
    job ?
__________________________________________________________________________

BNR, London Road, Harlow, Essex, CM17 9NA   Fax:   (+44) 279 454187



Sat, 15 Oct 1994 20:38:06 GMT
 Query Help!!

Quote:
>From: uunet!cbnewsb.cb.att.com!nll (neal.l.leitner)

>Subject: Query Help!!
>Date: 27 Apr 92 19:30:59 GMT
>X-Informix-List-Id: <news.1108>

>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:

>TABLE xyz (text CHAR(10), file_no INTEGER);

>One solution is:
>    select file_no from xyz where text = "{*filter*}" and file_no in
>    (select file_no from xyz where text = "MYSTERY")

>The other solution was:
>    select file_no from xyz where text = "{*filter*}" 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;

>Does anyone have a alternative solution to make these queries go faster?

I haven't checked on the speed, but you can try:

SELECT A.File_no
    FROM Xyz A, Xyz B
    WHERE A.File_no = B.File_no
      AND A.Text = "MYSTERY"
      AND B.Text = "{*filter*}"

This uses the features called table aliassing and self-joins.  The
extension to three categories is, I trust, obvious.  It is relatively
easily done in a programming language (ESQL or I4GL) and non-trivial
otherwise.

BTW: I see you are using Standard Engine: the word TEXT is the data type
for a TEXT BLOB in Online -- you would be better off using some other word
for the column name if you think you might be using OnLine at any future
time. (And, to the afficionado's, I do know that you can get away with
CREATE TABLE TABLE in sufficiently recent versions of Informix -- I just
don't think you should try!)

Yours,



Sat, 15 Oct 1994 23:48:08 GMT
 Query Help!!

->

Quote:
->neal.l.leitner writes:

->
->>       select file_no from xyz
->>       where text = "{*filter*}"
->>       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.
->

What about

        select file_no from xyz
    where  text in ("{*filter*}", "MYSTERY")
    group by file_no
        having count(*) >= 2

->If you can, is that slower/faster ?   (sorry, I don't have 130,000
->rows to play with at the moment  :-)

Sorry, too, I didn't have the time to test the above.
Well, maybe it helps or shows an alternative. :-/

->Or is the point that they can pick lots of keywords  (in which
->case I probably blew it completely)  ?   If so, you might UNION
->all the individual SELECTs into a temp table and then self-join
->that.

In that case you'd have to apply an array and a variable for the
WHERE resp. the HAVING clause.

->Good luck ...
->
From me as well.

->PS. It's bucketting down with rain.  Does anyone have a spare warm-climate
->    job ?

I thought, British get a tan by sitting in the British rain??? :-)
[ (c)Beatles ]

--

St.-Anna-Str. 13, D-W-8000 Muenchen 22 (Lehel),        +49-89-298934



Sun, 16 Oct 1994 23:13:32 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. query:HELP! HELP HELP HELP HELP

2. NULL Values in SQL Queries - HELP HELP HELP...

3. Query help with an 'averageing' query

4. Query/Sub-query help

5. NULL Values in SQL Queries - HELP HELP

6. Query Help

7. INSERT Query Help

8. SQL 2K English Query help!

9. query help .

10. Query help requested for Login information

11. Query help

12. query help appreciated. max()/joins/subquery


 
Powered by phpBB® Forum Software