Datatables being used on a large and complex query. Anyway to avoid using totalRecords???

ladieu

I have a highly optimized search query on a very complicated system. When limited to even 1000 rows it executes in about .004 seconds. However to run w/o a limit with this particular query may take as long as 35 seconds if the users search is broad enough.

My issue is with data tables I am forced to pass in the total number of results, which means I need to query mysql for a total row count. The act of getting this count (I've tried various methods) is taking 30 to 40 seconds and the query runs to display the page in far less than 1/2 of a second.

I am wondering if it is possible to go from page to page and just have data tables display whatever I feed it. So if it tries to access page 12 and there should only be 11 pages that is fine, my ajax source will just display an empty result set.

I hope this makes some sense. Grabbing the row count is destroying my performance.

My backup option is custom code so I am really hoping someone out there has an answer

ladieu

I hacked this by hardcoding the total records to a number much later than my largegst possible result and then I used CSS to hold the "page X of X text..

Not optimal but it will serve for now

-Nick

allan

The basic answer is no - at the moment DataTables needs to know exactly how many records there are in the result set (so it can enable / disable pagination controls as needed). However it either 'soft' or indeterminate in future is a really nice enhancement idea and something I will look at adding in future.

Until then, your hack is as about as good as it gets... :-)

Allan