how to optimise join queries for using with jquery datatable?

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.

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?

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

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

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.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide