query with multiple or vs union multiple queries, which one will be faster?

As the name suggests, a query with multiple or will be faster or a query with multiple query joined with union will be faster? I have joined only two tables to make it simple but there can be more tables in join.

for example,

    select isbn, booktitle from book as b left join publisher as p
        on b.publisherid = p.publisherid
        where 
           booktitle like '%mysql%' or
           isbn like '%mysql%' or
           publishername like '%mysql%'  

vs

      select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    booktitle li开发者_如何学Goke '%mysql%'  
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
   where 
    isbn like '%mysql%' 
   union 
   select isbn, booktitle from book as b left join publisher as p
   on b.publisherid = p.publisherid
    where 
    publishername like '%mysql%'  

I don't see how your union's would be faster. You're having to scan and join 3 times, where as in the first one, you're only scanning once, and for each joined record you can apply the criteria.

The top one will be more efficient since you're only querying the data once. That said, if you're querying against data you will not be returning, it's even better to put those criteria in an IN clause in the WHERE. In your provided example, it would look like this:

SELECT isbn, booktitle
FROM book as b 
WHERE 
   booktitle LIKE '%mysql%' or
   isbn like '%mysql%' or
   b.publisherid in (SELECT publisherid FROM publisher WHERE publishername like '%mysql%')

With proper indexes on the filtering columns (publishername, in this case), MySQL will optimize each piece and combine them much more efficiently.

The second one will be faster.

Here is the analysis:

http://books.google.com.sg/books?id=iaCCQ13_zMIC&pg=PT118&lpg=PT118&dq=In+this+example,+MySQL+uses+the+ExpireTime+index+to+fetch+a+set+of+rows&source=bl&ots=3FJ1-70Hq3&sig=V_0z7nSV7_hGwW_fC8IWmFI7ZLw&hl=en&sa=X&ei=Xx1uUan5IsulrQfh04GIBA&ved=0CDQQ6AEwAA