Saving time in queries on sql

Question

I have a scenario. I have say 300 records in my table. I execute a query to get the total count. Then , since i have to implement pagination,

I select the data from the same table using limits according t the count. I was thinking if i can get the count and data in a single query.? .

I tried below code:

Select * ,count(*) as cnt from table;

But this gave me the total count but only 1 record!

Is there a way to save my time exhausted in query and get results in a single query?

If you are doing pagination, using application code (php, .net, coldfusion, etc) might make this whole process easier. — Dan Bracuk, Mar 14 '13 at 14:05
@DanBracuk - Application code can't get a full table count on a `LIMIT`ed dataset. — Steven Moseley, Mar 14 '13 at 14:40

score 4 · Answer 1 · answered Mar 14 '13 at 13:50

4

something like:

select t1.*,t2.cnt
from table t1
cross join (select count(*) as cnt from table) t2 
limit 'your limit for the first page'

or

select *,(select count(*) from table) as cnt
from table
limit 'your limit for the first page'

answered Mar 14 '13 at 13:50

Dumitrescu Bogdan

7,127
2
23
31

can u please explain first answer once.I dont have that much knowledge – PSR Mar 14 '13 at 13:55
I join the table with it's count number (a table with one row and one column). I display everything from the table and count number from the second one. – Dumitrescu Bogdan Mar 14 '13 at 13:58
But Using this I would have to execute the count query every time. Isn't it? – Engineer Mar 15 '13 at 05:32
The sql engine is smart enough not to do that. – Dumitrescu Bogdan Mar 15 '13 at 09:51

score 0 · Answer 2 · edited May 23 '17 at 12:28

0

Using two queries might not be as bad as you may think, you can read this for more information.

edited May 23 '17 at 12:28

Community

1
1

answered Mar 14 '13 at 13:57

tonyjmnz

536
4
13

`SQL_CALC_FOUND_ROWS` is actually slower than running a separate `COUNT()` query. – Steven Moseley Mar 14 '13 at 14:00

score 0 · Answer 3 · answered Mar 14 '13 at 14:01

You can get information in data structure you mentioned, but there is really no reason to do it. There is no performance problem when you do two queries - one for getting rows count and another for data selection. You don't save anything when you try to select all information in one query. Do two simple queries instead, it will be better solution for your app - you will preserve its simplicity and clarity.

Saving time in queries on sql

3 Answers3