Re: SQL Query Question

[sulewski]

I think I figured out the time problem. If I make s2 in the or s1 and 
remove any instances of s2 it works very fast with the 'or'.

Joe

On Tuesday, January 20, 2004, at 09:50  AM, sulewski wrote:

Hello,

For my final solution I decided to use the inner join method. The 
query is created dynamically based upon a user interface component 
that allows people to build queries using parenthesis, ands and or's.  
Plus there is another field that I didn't include in the original 
question so as to keep the problem focused.  So here is the basic 
structure of what I did,

each query starts with the standard
select table1.*,table2.* from table1,tabl2,
then I append the inner joins for each search able field. The user can 
choose to search for one value or many values. Realistically I don't 
expect this to go above 3 to 5 fields.
searchtable s1,searchtable s2
Then I include the queries
where (table2.id=s1.rdid and then the dynamic part

((s2.vid=10109 and s2.value>=5) and (s1.vid=10089 and s1.value>4000)))

now I make sure all the searchtables are matched
and (s1.rdid=s2.rdid)
then the rest of my query which binds table1 to table2 but that 
irrelevant to this discussion
and blah blah blah

Here is an example
select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2 where (table2.id=s1.rdid and ((s2. vid =10109 and s2.value>=5) and 
(s1. vid =10089 and s1.value>4000))) and (s1.rdid=s2.rdid) and blah 
blah blah

This works really fast for a table with about 20,000 records.  I hope 
it works against a large table. But unfortunately when I add an 'or' 
it really slows down by about 5 fold.

select table1.*,table2.* from table1,table2,searchtable s1,searchtable 
s2, searchtable s3 where (table2.id=s1.rdid and ( ((s3. vid =10109 and 
s3.value>=5) and (s1. vid =10089 and s1.value>4000)) or (v2. vid 
=10096 and v2.value=10))) and (s1.rdid=s2.rdid and s2.rdid=s3.rdid) 
and blah blah blah

Thanks for everyone's help! I can't tell you how much I appreciate it.

Joe

On Monday, January 19, 2004, at 07:30  PM, Jochem van Dieten wrote:

Michael Satterwhite wrote:
On Monday 19 January 2004 16:30, Jochem van Dieten wrote:
Michael Satterwhite said:
On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
So let's make it 2 fields:
SELECT
  t1.*
FROM
  table1 t1,
  table2 t2 INNER JOIN table2 t3
   ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554)
WHERE
  t1.rdid = t2.rdid
Add GROUP BY/DISTINCT per your requirements.
Although you're giving Table2 two aliases (t2 and t3) there is 
still
only two  tables and *ONE* field. In the join listed above, you are
asking for the  records where t2.rdid = t3.rdid (*NOT* what you 
want
to do, you have now left  t1 out of the join altogether) plus ???
(I'm not sure what this would match,  although it looks
interesting).
Why not hold of judgement until you are sure what it would match?
It's only the second part of the join that I'm not sure of
Then why not hold of judgement until you are sure ?


Table1 (t1) isn't used at all in the join parameters.
Not all joins are specified using the join keyword.


As records from table1 are required in the result, this won't work 
as desired.
Would you please just create the tables and compare all the offered 
suggestions?

Jochem

--
I don't get it
immigrants don't work
and steal our jobs
    - Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]