speed full text search index using codeigniter is not working as much as speed

Question

am having the issues in full-text search using CodeIgniter. here, am having one table with dynamic rows and columns, when I searching input field to one row using full-text index is coming by very slowly. here have already set the following values as DB engine as MYISAM and table column as FULLTEXT INDEX. I want the data in input field as very much fast. here have I attached what I tired. please check it and let me know.

public function product_autocomplete()
{
  $name_startsWith = $_POST['name_startsWith'];
  $type = $_POST['type'];
  $row_num = $_POST['row_num'];
  $this->db->like('item_number', $name_startsWith);
  $query = $this->db->select('item_id,item_number,name,tax_included,cost_price,categoryid,unit_price,supplier_id,quantity_received')
  ->from('bgs_items')
  ->where('MATCH (bgs_items.item_number) AGAINST ("'. $name_startsWith .'")', NULL, false)
  ->limit(10)
  ->get();
  $data = array();

  foreach ($query->result_array() as $row)
  {
    $name = $row['item_number']."-".$row['name'].'|'.$row['name'].'|'.$row['tax_included'].'|'.$row['cost_price'].'|'.$row['categoryid'].'|'.$row['unit_price'].'|'.$row['supplier_id'].'|'.$row['quantity_received'].'|'.$row['item_number'].'|'.$row['item_id'].'|'.$row['supplier_id'].'|'.$row_num;
    array_push($data, $name);
  }
  echo json_encode($data);
}

why are you using `like` and `against` both in same query? – Nishant Nair Feb 27 '17 at 09:16 — Nishant Nair, Feb 27 '17 at 09:16
yes what query i want to used for getting data from db – user3839366 Feb 27 '17 at 10:21 — user3839366, Feb 27 '17 at 10:21

score 0 · Answer 1 · answered Feb 27 '17 at 10:30

Please try using below query

    public function product_autocomplete()
    {
      $name_startsWith = $_POST['name_startsWith'];
      $type = $_POST['type'];
      $row_num = $_POST['row_num'];
      $query = $this->db->select('item_id,item_number,name,tax_included,cost_price,categoryid,unit_price,supplier_id,quantity_received')
      ->from('bgs_items')
      ->where('MATCH (bgs_items.item_number) AGAINST ("'. $name_startsWith .'")', NULL, false)
      ->limit(10)
      ->get();
      $data = array();

      foreach ($query->result_array() as $row)
      {
        $name = $row['item_number']."-".$row['name'].'|'.$row['name'].'|'.$row['tax_included'].'|'.$row['cost_price'].'|'.$row['categoryid'].'|'.$row['unit_price'].'|'.$row['supplier_id'].'|'.$row['quantity_received'].'|'.$row['item_number'].'|'.$row['item_id'].'|'.$row['supplier_id'].'|'.$row_num;
        array_push($data, $name);
      }
      echo json_encode($data);
    }

speed full text search index using codeigniter is not working as much as speed

1 Answers1