Hi. I just finished a database that will be supporting another system,
and right now it is running on Postgres, but I was unhappy with that for
various reasons. I converted my data to mysql, and most things look
good: in the ways I care about, performance is much better, EXCEPT for
one issue which is unfortunately one of the most-performed operations.
I need to frequently do a query that does an "OR" on two columns. For
example,
SELECT * FROM sales WHERE seller='bob' OR buyer='bob';
(the actual query is much more complex than this, but you get the idea).
Under both Postgres and mysql this turns into a scan of the entire
(largish) table, even though I have indexes for both "seller" and
"buyer". Under Postgres, I was able to make this fast by rewriting it
as:
SELECT * FROM sales WHERE seller='bob' UNION SELECT * FROM sales
WHERE buyer='bob';
This became very fast! Two index scans in a row, then a combine step at
the end, no longer any need to scan the whole table, and the end result
was exactly the same. I have found that mysql doesn't have union, can
anybody on this list think of a similar way to rewrite the query that
will work under MySql and gets good performance? Unfortuantely this will
force me to stay with Postgres if I can't do it efficiently, because I
do it often and it takes *way* too long under MySql right now. It would
really be a shame, because all my other queries are much faster under
MySql... :-(
Anyway, any ideas are appreciated. Thanks.
PS - I considered just doing two selects, then combining them in my
client...but by the time I write the code to combine the two result
sets, sort them, etc., it seems like the database just isn't making my
life easier any more, and that was the whole point of using it!
--
Bill Shubert ([EMAIL PROTECTED])
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
