How to Optimize CodeIgniter Query Performance?

Question

In one of my client project, I use CodeIgniter, MySQL and Bootstrap 4 and it works properly until recently when the volume of data raised dramatically. Due to large amount data, one list page requires almost one minute to load all data. The PHP code behind the list page is:

function getproductlist() {
    $query = $this->db->select('prd.id id, prd.brand_name brand_name, prd.strength strength, prd.status status, prd.remarks remarks, com.company_name company_name, cat.category_name category_name, dos.type_name type_name')->from('bsoft_pharma_product prd')->join('bsoft_pharma_company com', 'prd.company_id = com.id', 'left')->join('bsoft_pharma_product_category cat', 'prd.category_id = cat.id', 'left')->join('bsoft_pharma_product_type dos', 'prd.dosage_id = dos.id', 'left')->get();
    return $query;
}

Where bsoft_pharma_product table contains around 27000 data, bsoft_pharma_company table contains 170 data, bsoft_pharma_product_category table contains 1200 data and finally bsoft_pharma_product_type table contains 112 data.

Can anyone tell me how can I optimize my query?
Thanks

Then don't load it all at once, use ajax based pagination, and look into caching it. — Lawrence Cherone
– Lawrence Cherone, Commented Mar 6, 2018 at 8:52
@Akintunde007: Not fully AJAX, I just use jQuery $.load("") and generate the table on CI controller side. — Shimul
– Shimul, Commented Mar 6, 2018 at 8:54
@AbdullaNilam: Do you think SQL view is an optimized solution? — Shimul
– Shimul, Commented Mar 6, 2018 at 8:56

msz · Accepted Answer · 2018-03-09 05:43:08Z

1

As I think there might be some solutions you have to try...

Solution 1:

Load the page data only when user reach to the End of the page... Like: Google Images: [When we are viewing google images and we reach to the end of the page then some others images starts loading until the requested images types End.

Solution 2:

Convert the data to pagination like 20 / 30 records per page.

Solution 3:

Use some logo / animation loading tricks... like:

Use JavaScript to check the page loading percentage and then load something like: animation or company logo etc, so if the user have to wait then they don't feel boar or thinks that there is some issue in the page.

answered Mar 9, 2018 at 5:43

msz

3091 gold badge2 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to Optimize CodeIgniter Query Performance?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related