Hi. On Sun 2002-07-28 at 17:58:36 -0700, [EMAIL PROTECTED] wrote: > hello, > > i have a couple of questions about my SQL query. > > 1. if i want all rows in table 1 that may or may not have a counterpart > row in table 2, i use LEFT JOIN. this is HORRIBLY slow! why? if i make it > a simple join (i.e., without the words "LEFT JOIN") then it is a little > faster.
Well, what now? It is "HORRIBLY slow" or without it "a little faster"? Those both do not describe the same for me. How about providing some real numbers, eliminating any bias this way? LEFT JOINs have to slower, because they have to do some more work. The difference should be relatively small, except for pathological cases (e.g. almost no match in the right table). http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html > 2. secondly, i need to have an OR in my join clause. > > where > (table1.col1 = table2.col1 or table1.col2 = table2.col1) You can rewrite that as table2.col2 IN ( table1.col1, table1.col2 ) which should make use of indexes for range search. Hm. I thought the MySQL optimizer does this kind of OR already, but maybe this was only in 4.x. > this is for a search procedure. if i remove the OR, it is much faster. > how can i get around this? i need to have the OR because of the search > engine in the website, but i need it to be fast. MySQL does not use indexes for most OR clauses (yet). A list of when MySQL makes use of index can be found here: http://www.mysql.com/doc/M/y/MySQL_indexes.html > 3. based on the OR condition, what kind of indices can i use? See above. Greetings, Benjamin. -- [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
