60,000 rows getting 5 mints to load in

zazikhan1

I have sixty thousand rows in database and when i fetch it took more than 5 mins to get fetched. I am using Codeigniter as server side scripting language and i want to retrieve them fastly please some one help me out

The Datatable code..

 var oTable = $('#itable').dataTable({
                "bServerSide": true,
                "sPaginationType": "full_numbers",
                "sAjaxSource": "<?php echo base_url() ?>inventory/getStockLists",
                "sServerMethod": "POST",
                "aaSorting": [[0, 'asc']],
                "bDeferRender": true,
                "bJQueryUI": true,
                "bProcessing": true,
                "aoColumns": [
                    {"aaData": "0", "sSortDataType": "dom-text", "sType": "numeric", 'sClass': "center"},
                    {"aaData": "1"},
                    {"aaData": "2", 'sClass': "center"},
                    {"aaData": "3", 'sClass': "center"},
                    {"aaData": "4", 'sClass': "center"},
                    {"aaData": "5", 'sClass': "center"},
                    {"aaData": "6", 'bSearchable': false, 'sClass': "center"},
                    {"aaData": "7", 'bSearchable': false, 'sClass': "center"},
                    {"aaData": "8", 'sClass': "center"},
                    {"aaData": "9", 'sClass': "center"},
                    {"aaData": "10", 'sClass': "center"},
                    {"aaData": "11", 'sClass': "center"},
                ],
                "fnRowCallback": function (nRow, aData, iDisplayIndex) {
                    $('td:eq(13)', nRow).html('<a class="btn btn-primary btn-xs btn-details" data-href="' + aData['13'] + '"><i class="fa fa-chevron-down"></i></a>');
                    return nRow;
                }
            });

zazikhan1

Ok i have solved the problem Never use left or right join if u are using CODIEGNITER DATATABLE library

public function getStockLists($outlet_id = false) {

            $this->load->library('datatables');
            $this->datatables->select('stock_num,part_no, description, remark, brand,model_no,SUM(qty) as qty,SUM(qty) as qty_hand, cost_price, price1, pricing_info,locations.location_name')
                ->from('stock');
            $this->datatables->join('stock_outlets', 'stock_outlets.item_id=stock.item_id');
            $this->datatables->join('locations','stock.item_id=locations.item_id','left'); //wrong
            $this->datatables->join('locations','stock.item_id=locations.item_id'); --> RIght
            if ($outlet_id) {
                $this->datatables->where('outlet_id', $outlet_id);
            }
            $this->datatables->group_by('stock.item_id');
            //$this->datatables->edit_column('supplier_id', 'All', '');
            return $this->datatables->generate();
        }