MySQL: How to COUNT the number of rows returned by a Complicated Query

This feels like it should be really easy, but I just can't get it and I've tried everything.

How can I just get the NUMBER OF ROWS returned by this query:

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col7 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2' 
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
        AND Alias2.Col5 LIKE 'blah6' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

UNION 

(SELECT Alias1 . *, 
        Alias2 . * 
 FROM   Table1 AS Alias1 
        LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
 WHERE  Alias1.Col1 != 'blah1' 
        AND Alias1.Col2 LIKE 'blah2'
        AND ( Alias1.Col3 LIKE 'blah3' 
               OR Alias1.Col3 LIKE 'blah4' ) 
        AND Alias1.Col4 = 'blah5' 
 ORDER  BY Alias1.Col6 DESC 
 LIMIT  50) 

I suppose I could just use PHP to execute the query and then do a mysql_num_rows on the results, but I want to do it directly with SQL because I heard that's faster as it saves a step.

Thanks!

EDIT:

The following is giving me error (#1060 - Duplicate column name 'Col1'):

   SELECT COUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) 

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50)
   ) a

The following is giving me error (#1064 - You have an error in your SQL syntax; ):

 SELECT C开发者_开发问答OUNT(*) FROM (
   (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col7 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) a

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2' 
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
            AND Alias2.Col5 LIKE 'blah6' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) b

    UNION 

    (SELECT Alias1 . *, 
            Alias2 . * 
     FROM   Table1 AS Alias1 
            LEFT JOIN Table2 AS Alias2 USING ( Col1 ) 
     WHERE  Alias1.Col1 != 'blah1' 
            AND Alias1.Col2 LIKE 'blah2'
            AND ( Alias1.Col3 LIKE 'blah3' 
                   OR Alias1.Col3 LIKE 'blah4' ) 
            AND Alias1.Col4 = 'blah5' 
     ORDER  BY Alias1.Col6 DESC 
     LIMIT  50) c
   ) z

---

Wrap your entire query in parentheses (make it a subquery) and do a select count(*) from it:

select count(*) from (YOUR HUGE QUERY UNION YOUR HUGE QUERY) a

---

You need to specify a unique alias for your Column Col1 since it is used in a join which requires unique columns to match against each other. It will throw an error 1060 if it comes across duplicated columns. Check this: Duplicate column names in SQL query

In your case, the MySQL gets confused with Col1 in table 1 and table 2 and hence the error. Once you have used that, you can SELECT COUNT(*) from YourQuery

---

Wrap the whole query as

SELECT COUNT(*) FROM (<Your query>)

---

Really use mysql_num_rows. For the union the database needs to build a temporary table anyway, which is the costly thing. Even if it were possible to do straight within SQL you won't save much by asking MySQL to give you that number.

The data isn't sent to PHP when you just use the mysql_num_rows function anyway.

---

You need to specify a unique alias for your Column Col1 since it is used in a join which requires unique columns to match against each other. It will throw an error 1060 if it comes across duplicated columns. Check this: Duplicate column names in SQL query

In your case, the MySQL gets confused with Col1 in table 1 and table 2 and hence the error. Once you have used that, you can SELECT COUNT(*) from YourQuery