mysql - Get total number of rows from subquery? -

i have query like

select row1, row2 (     select b.row21, count(b.row21) hits     table2 b     b.row22 = 'foo' or b.row22 = 'bar'     group b.row21     having hits = 2 ) b inner join table1 on (b.row21 = a.row1) row2 = 123 limit 10 

now clearly, result limited first additional where , limit.

so how retrieve amount of rows returned subquery without needing execute seperately?

you can in mysql using variables:

select row1, row2, @rn numrows (     select b.row21, count(b.row21) hits, @rn := @rn + 1     table2 b cross join          (select @rn := 0) const     b.row22 = 'foo' or b.row22 = 'bar'     group b.row21     having hits = 2 ) b inner join table1 on (b.row21 = a.row1) row2 = 123 limit 10; 

you run risk mysql could rewrite query in different way, might avoid generation of rows. sql compilers smart enough this. don't think mysql smart.