Re: Indexed searching with OR ?

[Chris Nolan]

Hello again!

Andreas Pardeike wrote:

On 2004-02-16, at 14.13, Chris Nolan wrote:

MySQL's optimizer has a slight problem. OR queries cause it to get very
confused.
Try the following to get the best performance:

Rewrite SELECT FROM table WHERE (condition1) OR (condition2);

As:

(SELECT FROM table WHERE condition1) UNION (SELECT FROM table WHERE
condition2);
Hope this helps!


Then what would be your recommendation if I have the following search 
form:

   Search  [A]  in  [field list A]  ['AND'/'OR'/'AND NOT']
   Search  [B]  in  [field list B]  ['AND'/'OR'/'AND NOT']
   Search  [C]  in  [field list C]  ['AND'/'OR'/'AND NOT']
   ...
( '[]' indicating popup's or text fields )

So far, I was building my query the normal way and it was already pretty
complex code because of many different joins. Now, with your suggestion
of UNION (which I tested and found working) everything gets VERY complex
because I can't see an easy way to do an AND.
Hmm....forgive me, but I am about to resort to boolean algebra.

(A OR B) AND C = (A AND C) OR (B AND C) .

Thus:

(SELECT * FROM table WHERE (condA AND condC)) UNION (SELECT * FROM TABLE 
WHERE (condB AND condC));

I guess you could rewrite you queries using the above method, or bug the 
MySQL team to prioritise the modification to the optimiser.

I.e. if the users choses 'Cond. A' OR 'Cond. B' AND 'Cond. C', I have
the problem that I can do a UNION on 'Cond. A' and 'Cond. B' (which in
themselves can be complex) but I have no idea how to implement an 'AND'
like between 'Cond. B' and 'Cond. C'.
Is there a corresponding 'AND' version of the 'UNION' ?

Andreas Pardeike


Regards,

Chris

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