+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: how to optimise join queries for using with jquery datatable? (/showthread.php?tid=1571)
how to optimise join queries for using with jquery datatable? - shivraj.chari - 03-22-2015
hello
i am using jquery datatable for my project ,which is developed in codeigniter.I am using server side processing for datatable using ajax.To display data it is doing 4 table join queries.each table has 12k records.but when i run the query it takes lotsa time to display the result and the server eventually timeouts.
I am using Ignited Datatables wrapper for using data table with codeigniter.
any help will be appreciated
thanks in advance.
RE: how to optimise join queries for using with jquery datatable? - CroNiX - 03-22-2015
Are your db tables properly indexed for the fields you are joining on, ordering by, etc? I have more joins and more rows than that on some queries that take less than a second, but they have to be properly indexed. Did you run an EXPLAIN query to see where the bottlenecks are?
RE: how to optimise join queries for using with jquery datatable? - musicalangel - 03-22-2015
(03-22-2015, 05:07 PM)shivraj.chari Wrote: hello
i am using jquery datatable for my project ,which is developed in codeigniter.I am using server side processing for datatable using ajax.To display data it is doing 4 table join queries.each table has 12k records.but when i run the query it takes lotsa time to display the result and the server eventually timeouts.
I am using Ignited Datatables wrapper for using data table with codeigniter.
any help will be appreciated
thanks in advance.
Greetings,
Try CroNIX suggestion first.
IMO,
if all your table are so much data, try limiting the table data before join. for example
Code:
select t1.field1, t1.field2, table2.field1, table2.field2 from
(select field1, field2 from table1 where ...) as t1
join table2 on table2.id=t1.field2
...if fully join 4 table with 12k row each, the total cost will very expensive.
(12K * 12K* 12K* 12K)=12K^4
RE: how to optimise join queries for using with jquery datatable? - shivraj.chari - 03-23-2015
yeah i will try CroNIX for sure ,and i will update you soon .thank you soo much
RE: how to optimise join queries for using with jquery datatable? - mwhitney - 03-23-2015
Additionally, make sure you're limiting the query to the parameters datatables needs for the current page (or filter, or whatever action it is taking). If you're selecting the full data set, there's no point to doing server-side processing, because datatables could do everything on the client if it had the full dataset from the start.