Datatables speed up content display

Question

In Datatables I use two query one to fetch records in that particular date range with server side pagination and another to get the total count of records in that particular date range. I am using Postgresql database.

select * from daily_txns where created_date <='2017-08-01' and created_date>='2017-07-01' and mid='0446721M0008690' order by created_date desc limit 10;

select count(mid) from daily_txns where created_date <='2017-08-01' and created_date>='2017-07-01' and mid='0446721M0008690';

Is this the correct way to do or is there any best approach for the same. If the first query takes 20 sec then the second query takes 40 sec and the total time taken to display the result is more then 60 sec. How to overcome this problem.

How long does it take to run each query in the database without loading anything into Datatables? — bhttoan, Aug 01 '17 at 14:23
In first query you can get first 10 record and then you can give `order by` condition — Yashvantsinh Zala, Aug 01 '17 at 14:24
And for second query, as per SQL stander you can't reduce execution time with `count` , `order by` condition, because it's check all rows from your table — Yashvantsinh Zala, Aug 01 '17 at 14:27

score 0 · Accepted Answer · answered Aug 01 '17 at 14:26

0

If you are using postgres you can use this to reduce everything to one single query

select *, count(*) OVER() AS full_count from daily_txns where created_date <='2017-08-01' and created_date>='2017-07-01' and mid='0446721M0008690' order by created_date desc limit 10;

the "count(*) OVER() AS full_count" will 'ignore' the limits passed giving you the full count of possible elements

answered Aug 01 '17 at 14:26

Brugolo

4,685
3
22
14

Can you please explain how can it's reduce execution time? – Yashvantsinh Zala Aug 01 '17 at 14:28
This is taking me much more time then executing both separately – Vijayanand Premnath Aug 01 '17 at 14:33
How may records are in total on daily_txns table? Do you have any index on the table? What db are you using? – Brugolo Aug 01 '17 at 14:42
yes I have index for created_date and mid. I am using postgress database. There are 2,50,00,000 records in it. – Vijayanand Premnath Aug 01 '17 at 14:59

Dmitriy.Net · Answer 2 · 2017-08-01T14:52:00.643

Solution depends on your table indexes and this answer only if indexes correct and you solutions like count(*) doesn't help you.

But sometimes you can try to use flag SQL_CALC_FOUND_ROWS for your query, but I recommend to read this question and answer before use it.

Solution:

select SQL_CALC_FOUND_ROWS * from daily_txns where created_date <='2017-08-01' and created_date>='2017-07-01' and mid='0446721M0008690' order by created_date desc limit 10;

And query for get total records for your query

SELECT FOUND_ROWS() as cnt

score 0 · Answer 3 · answered Aug 01 '17 at 15:46

You need to add an index to the columns mid and created_date on the table daily_txns. I'm also guessing that mid will be more restrictive than created_date, so I recommend using it first in the query so that the search reduces faster.

select * from daily_txns where mid='0446721M0008690' created_date <='2017-08-01' and created_date>='2017-07-01' order by created_date desc limit 10;

select count(mid) from daily_txns where mid='0446721M0008690' and created_date <='2017-08-01' and created_date>='2017-07-01';

Datatables speed up content display

3 Answers3