Speed up search results MySQL and CodeIgniter3

General
1

I have an ecommerce coded in codeIgniter3 with a search bar. Problem is caused for example when i search for ‘notebook’ as keyword, because there are thousand of result.

In my controller i have:

if (!$this->input->get('search')) {
        
            switch ($type) {
                case '1':
                    $category = $this->categoria->findBread($id_category);
                    $category->id_subcategory = 0;
                
                    break;
                case '2':
                    $category = $this->subcategoria->findDetail($id_category);

                    break;


                case '3':
                case '8':

                    $menu = [];
                    $tipoBarra = 'search';
                    // $filtroProd = $products;
                    $search = $cadenaPaginacion;
                    $filtroProd = $this->producto->GetProductsgLike($search);
                    $uniqueIds = [];
                    

                    $uniqueCategories = [];

                    foreach ($filtroProd as $item) {
                        if (!in_array($item->id_category, $uniqueIds)) {

                            $uniqueIds[] = $item->id_category;

                            $uniqueCategories[] = [
                                'id_category' => $item->id_category,
                                'name' => $item->name,
                                'id_padre' => $item->id_padre,
                                'cadenaBusqueda' => $search,
                            ];
                        }
                    }

                    break;

                default:

                    $tipoBarra = 'def';
                    $uniqueCategories = $this->categoria->menu();
            }
        } else {

            $menu = [];

            $tipoBarra = 'search';
            $search = $this->input->get('search');
            $filtroProd = $this->producto->GetProductsgLike($search);
    
            $uniqueIds = [];

            $uniqueCategories = [];

            foreach ($filtroProd as $item) {
                if (!in_array($item->id_category, $uniqueIds)) {

                    $uniqueIds[] = $item->id_category;

                    $uniqueCategories[] = [
                        'id_category' => $item->id_category,
                        'name' => $item->name,
                        'id_padre' => $item->id_padre,
                        'cadenaBusqueda' => $search,
                    ];
                }   
                
                if ($id_category != '') {
                $category = $this->categoria->findBread($item->id_category);
                $category->id_subcategory = 0;
            }
            }
            $data = array(
                'categoria_sesion' => $uniqueCategories,
        
            );
            $this->session->set_userdata($data);
        ////    var_dump($data);


            $menu = $this->categoria->buscarcategoria($filtroProd, false, true, $search);

        
        }

        $vista_interna = array(
            'tipoBarra' => $tipoBarra,
            'load_Products' => $products,
            'categories' => $categories,
            'subcategories' => $subcategories = [],
            'links' => $links,
            'category' => $category,
            'menu' => $uniqueCategories,
        );
    

        if ($search == "0") {
            // $vista_interna['search'] = '';
            if ($id_category != 0) {
                $cate = $this->categoria->find($id_category);

                $vista_interna['search'] = $cate->name;
            } else {
                // $vista_interna['search'] = $search;
            }
        } else {
            if ($id_category != 0) {
                $cate = $this->categoria->find($id_category);

                $vista_interna['search'] = '<br>' . $cate->name . ' - ' . $search;
            } else {
                $vista_interna['search'] = $search;
            }
        }

        $vista_config = array(
            'metadata' => '',
            'title' => '',
            'description' => '',
        );

        $vista_externa = array(
            'contenido_main' => $this->load->view('frontend/public/products', $vista_interna, true),
            'configuracion' => $this->frontend_lib->configuraciones($vista_config),
            'configurations' => $this->codegen_model->get('configurations', '*', 'id_configuration > 0'),

        );

and the function getProductsgLike you could see aboveis like that:

    public function GetProductsgLike($text)
    {
        $this->db->select('products.id_product AS id, products.name AS text,categories.id_category, categories.name as name,categories.id_padre');
        $this->db->join('categories categories', 'products.id_category = categories.id_category');
        $this->db->where('stock >', 0);
        $this->db->where('products.active', ACTIVE);
        $this->db->where('categories.id_padre!=',"0");
        $this->db->like('products.name', $text);
        $this->db->order_by('name', 'asc');
        $this->db->order_by('products.name', 'asc');
        $query = $this->db->get($this->table);
  
        return $query->result();
    }

Altough i have pagination of 6 records per page, the query give up to 5000 records.

enter image description here

Probably i can afford it with MySQL, but what technique i can use?

2

1. Limit Search Results Programmatically:

  • In GetProductsgLike Function:
    • Add a LIMIT clause to the database query to restrict the number of returned rows:
      PHP
public function GetProductsgLike($text, $limit = 6) { // Adjust limit based on your pagination
    $this->db->select('products.id_product AS id, products.name AS text, categories.id_category, categories.name as name, categories.id_padre');
    // ... other query parts
    $this->db->like('products.name', $text);
    $this->db->order_by('name', 'asc');
    $this->db->order_by('products.name', 'asc');
    $this->db->limit($limit); // Limit results to a manageable number
    $query = $this->db->get($this->table);
    return $query->result();
}

In Your Controller:

  • Call GetProductsgLike with the desired limit based on your pagination:

PHP

$search = $this->input->get('search');
$filtroProd = $this->producto->GetProductsgLike($search, 6); // Pass limit

2. Leverage Database Indexing:

  • Create indexes on the name column of the products table and the id_category column if you’re using it for filtering. This can significantly speed up search queries.

3. Implement Search Faceting (Optional):

  • If your product data allows for faceting (e.g., brand, price range), consider adding this feature to refine search results and reduce the number of items fetched at once.

4. Consider Search As You Type (Optional):

  • If applicable, implement search-as-you-type functionality using JavaScript (e.g., AJAX requests) to display suggestions or filtered results based on partial user input. This can minimize unnecessary database queries.

Additional Tips:

  • Pagination Optimization: Ensure your pagination logic handles large datasets efficiently. Consider techniques like offset-based pagination.
  • Caching (Advanced): Explore caching mechanisms (e.g., CodeIgniter’s cache library) to store frequently accessed search results, reducing database load.

Remember: The best approach depends on your specific use case, database size, and performance requirements. Analyze your application’s usage patterns and database structure to choose the most appropriate combination of strategies.

By implementing these techniques, you can effectively handle large search queries in your CodeIgniter 3 application, providing a more user-friendly and performant search experience.