CODEWITHSUNDEEP
mysqlsqldatabasequery-optimization
William Buttlicker
William Buttlicker

Reputation: 6000

Saving time in queries on sql

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?

Upvotes: 0

Views: 148

Answers (3)

tonyjmnz
tonyjmnz

Reputation: 536

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

Upvotes: 0

user2148736
user2148736

Reputation: 1323

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.

Upvotes: 0

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

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'

Upvotes: 4

Related Questions