how to optimise join queries for using with jquery datatable?

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