Union vs multiple ORs in where clause

[Scorpy]

I have a query that has 3 left joins on it from other tables, i'll write an example below (MySQL 5.1.32)..

 

(All conditions are key'd)

 

   SELECT tb1.*, tb2.*, tb3.* tb4.*
   FROM tb1
   LEFT JOIN tb2
   ON tb1.col = tb2.col
   LEFT JOIN tb3 
   ON tb3.col = tb1.col
   LEFT JOIN tb4
   ON tb4.col = tb1.col
   WHERE tb1.col1 = value OR tb1.col2 = value OR tb1.col3 = value 
   ORDER BY tb1.col4 ASC 

 

The problem is that because I use an OR in the where clause, it does a full table scan, so I wondered if doing this within a union would be faster?

 

Example:

 

(
   SELECT tb1.*, tb2.*, tb3.* tb4.*
   FROM tb1
   LEFT JOIN tb2
   ON tb1.col = tb2.col
   LEFT JOIN tb3 
   ON tb3.col = tb1.col
   LEFT JOIN tb4
   ON tb4.col = tb1.col
   WHERE tb1.col1 = value 
)
UNION ALL
(
   SELECT tb1.*, tb2.*, tb3.* tb4.*
   FROM tb1
   LEFT JOIN tb2
   ON tb1.col = tb2.col
   LEFT JOIN tb3 
   ON tb3.col = tb1.col
   LEFT JOIN tb4
   ON tb4.col = tb1.col
   WHERE tb1.col2 = value 
)
UNION ALL
(
   SELECT tb1.*, tb2.*, tb3.* tb4.*
   FROM tb1
   LEFT JOIN tb2
   ON tb1.col = tb2.col
   LEFT JOIN tb3 
   ON tb3.col = tb1.col
   LEFT JOIN tb4
   ON tb4.col = tb1.col
   WHERE tb1.col3 = value 
)

 

This way instead of using the OR value, each where condition went inside of it's own union, the thing that I'm confused about is that the first time I ran this it came up with 0.04 sec time, on a small table, whereas the first one showed at 0.00 sec, but all of the 'types' on the union showed as either ref or eq_ref which isn't a full scan, except for the union result select_type.

 

So I was wondering if anyone could explain this to me and point me in the right direction please.

[JustLikeIcarus]

Im guessing the slowdown you saw is related to how UNION used its temporary table.  Even though you did UNION ALL it still has to throw everything into a temporary table.  Plus with the unions you went back to "tb1" several time. Where as using a join with the OR's your only going to hit the first table once.  The other tables youll hit more though.

 

Thy this im curious on its performance,

 

SELECT tb1.*, tb2.*, tb3.* tb4.*
FROM tb1, tb2, tb3, tb4
WHERE
   tb1.col = tb2.col
AND   tb2.col = tb3.col
AND   tb3.col = tb4.col
AND (
tb1.col1 = value 
OR tb1.col2 = value 
OR tb1.col3 = value
)
ORDER BY tb1.col4

[Scorpy]

The explain on that shows the exact same as my first one with the left joins, a full tablescan for tb1 (NULL for keys), and eq_ref for the other tables using primary key.

[JustLikeIcarus]

Yeah since nothing in there were clause is limiting the initial results from tb1 then your going to do a full table scan.  Only way to not do one would be to specify tb1.col = value for an indexed column.

[Scorpy]

tb1.col1, tb1.col2 and tb1.col3 are all indexed, it shows them as possible keys but just doesn't use any of them, is there something i'm not understanding or that I'm missing?

[JustLikeIcarus]

Yeah the issue is that your current query is pulling all records from tb1 as the very first thing it does.  It doesn't know whats in the other tables therefor it has to pull back everything and then examine them for matches.  If you want to make use of indexes you will need to add another AND specifying a certain key/value pair like tb1.column = 'apple' or whatever.

 

I may be able to provide more help if you let me know what the data is and what your trying to return from the query.

[fenway]

Yes, what are you trying to do?