0

I am working on a laundry management system and want to retrieve customer order details with customer name from two tables having 25000+ rows of data. My code is working fine but using joins its taking too long to fetch the result. Kindly help me optimize the query. thanks here goes my code that works fine buts takes more than a minute to return the data and almost crashes most of the time...

$this->db->select('users.first_name,customer_order.auto_id, customer_order.invoice_no, customer_order.order_date, customer_order.order_place_from, customer_order.customer_id, customer_order.total_qty, customer_order.discount, customer_order.disc_amt, customer_order.total_paid, customer_order.total_balance, customer_order.delivery_date, customer_order.amt_paidby, customer_order.order_status, customer_order.mode_of_payment_adv, customer_order.advance_paid, customer_order.mode_of_payment_bal, customer_order.balance_paid_date');
$this->db->from('customer_order');
$this->db->join('users', 'customer_order.customer_id=users.id');
$this->db->order_by("auto_id","desc");
$data['invoiceorder'] = $this->db->get()->result();

Limiting the record gives accurate timing but I need to fetch all records

  • you can use `limit` and `offset`, you mention you need all record then also limit and offset is best option – Devsi Odedra Jan 28 '20 at 06:11
  • @DevsiOdedra can you please explain a bit more? I'm not getting it. – Zeeshan Ahmad Jan 28 '20 at 06:23
  • if you need all data, you have to explain the background - because if you need it for a gui - then its totally nonsense to do that - if you need it for a cron then it may help to take a look at https://stackoverflow.com/questions/59665243/codeigniter-how-to-select-all-rows-in-a-big-table-without-memory-leake/59666355#59666355 – Atural Jan 28 '20 at 10:06
  • @sintakonte yes actually I am working on a maintenance project don't know who created this scenario, this record is going to be used in a gui where data tables are being used for pagination, search and file conversion. – Zeeshan Ahmad Jan 28 '20 at 10:29
  • then you have to change your datatable structure to server side processing - take a look at https://datatables.net/examples/data_sources/server_side and https://stackoverflow.com/questions/47885399/datatable-with-ajax-is-not-working-well-after-use-serverside-true - this should help you – Atural Jan 28 '20 at 10:32
  • If using joins is too slow with a 25.000 row table, you definitely need to stop looking at code-based workarounds and start looking as table indexes and stuff like that – Javier Larroulet Jan 28 '20 at 18:09
  • @JavierLarroulet, I tried that too but indexes didn't work for me, I guess i'll need to go with server side programming as per sintakone in the above comment – Zeeshan Ahmad Jan 29 '20 at 02:56
  • @ZeeshanAhmad what do you mean "indexes didn't work for me"? did you correctly index your tables and still not getting good speeds on your queries? Could you share an `explain` on your queries? – Javier Larroulet Jan 29 '20 at 13:35

1 Answers1

0

Use codeigniter pagination, limit records so it will work fast. https://codeigniter.com/user_guide/libraries/pagination.html

Ahmad Saad
  • 771
  • 6
  • 17