CODEWITHSUNDEEP
mysqlstored-procedures
Kamlesh Bhure
Kamlesh Bhure

Reputation: 1

find number of rows without using count() in mysql

Can anybody help me to find way to count number of rows found while running query. I want to run this query in stored procedure depending on the count i want to perform certain actions. I was trying to set variable value something like.

 Select abc_master_id,
        abc_name,
        abc_parent_id,
       (@row := @row +1)  rownum 
 from abc_master 
 where blah..  blah.. limit 18,9

Above query is completely wrong. But here I want to count or set some flag that records found are less than or equal to limit set to query i.e here 9. By doing this i will save one query for counting rows fetched by query.

I have tried using SQL_CALC_FOUND_ROWS and Found_rows() but it gives me total number of rows if query run without limit.

Upvotes: 0

Views: 3469

Answers (2)

bart
bart

Reputation: 7777

Replace your query with a similar one 

Select count(*)
from abc_master where blah.. blah..

and fetch the first row.

Mysql has improved over the years but I don't know if your engine supports the much simpler subquery:

select count(*) from (
Select abc_master_id, abc_name, abc_parent_id ... from abc_master
where blah.. blah..)

Thus: your original query but without the limit clause, between parens.

Upvotes: 1

Harold Sota
Harold Sota

Reputation: 7566

The solution:

SELECT abc_master_id, 
       abc_name,
       abc_parent_id,
       @i:=@i+1 AS rownum 
FROM abc_master ,(SELECT @i:=0) foo
where blah.. blah.. limit 18,9

Upvotes: 3

Related Questions