 | | | FOUND_ROWS & UNION | FOUND_ROWS & UNION 2006-07-04 - By Taras D
Back
Hi everyone,
I have a couple of questions invovling using FOUND_ROWS() and UNION.
The manual states that:
"The value of FOUND_ROWS() is exact only if UNION ALL is used. If
UNION without ALL
is used, duplicate removal occurs and the value of FOUND_ROWS() is
only approximate."
I am using UNION DISTINCT. In what way is the value approximate? I
have tried some test queries using UNION DISTINCT and it seems to give
the correct number of results (ie: FOUND_ROWS isn't including the rows
that appear twice). Perhaps the 'duplicate removal occurs' statement
means that FOUND_ROWS doesn't find the number of rows in the UNION of
the result sets because DISTINCT wasn't specified (ie: it gives the
number of rows after duplicates have been removed)?
If it is the case the sometimes/all the time FOUND_ROWS doesn't give
the correct number of distinct results, ss there anyway of getting
over this limitation apart from executing the query twice (once with
the limit and once without the limit)?
Thanks
Taras
Test code:
=========================
(select SQL_CALC_FOUND_ROWS * from s where ID <= 20)UNION(select *
from s where ID <= 50) LIMIT 0,30;
select found_rows();
Gives 50 results, which is the correct value. If duplicate entries
were being counted, the above query would result in 70 rows.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
|