• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
how to optimise join queries for using with jquery datatable?

#1
Sad 
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.
Reply

#2
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?
Reply

#3
(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
Reply

#4
yeah i will try CroNIX for sure ,and i will update you soon .thank you soo much
Reply

#5
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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.