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.
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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
