My query returns empty results with big tables. Any advice?

I have never had problems with CI so far, but yesterday I started a project in which I have to handle big amounts of data, the database has approximately 10m rows on several tables and I'm struggling with a simple search function


My controller looks something like this. Its need to retrieve the username of the user by entering the email, ID or viceversa:

PHP Code:

public function username_finder() {

  $type = $this->input->post('type');

  if ($type == 'email') {
    $this->form_validation->set_rules('input_string', 'Input String', 'trim|required|valid_email');
    if($this->form_validation->run() == FALSE) { } else {

      $input_string = $this->input->post('input_string');
      $result = $this->user_model->finder_engine($input_string, $type);
      return $result;
      }
   }
} 


The finder_engine on my model:

PHP Code:

public function finder_engine($input_string, $type) {


  if ($type == 'email') {
    $this->db->like('email', $input_string);
    $query = $this->db->get('members');
    return $query->result();
   }
} 


My members table looks like this:
id  | username | email | id_number | password

When I run this on the members table (10m rows) it returns and empty string.

I have tried with different versions but it always returns an empty array:

Code:

if ($type == 'email') {

   $query = "SELECT * FROM members WHERE email=?";
   $query = $this->db->query($query, array($input_string));
   return $query->result_array();

 }

I duplicated the table and cut it off to 25000 rows, still empty, but when I run the same queries on the same table with 5000 records it returns the values perfectly. I guess this is a problem with the memory or with the execution time, but I have no idea. How can I approach this properly? What are good measures for CI regarding handling this amount of data?

Thanks a lot in advance!

PHP Code:

$this->db->like('email', $input_string); 


First thing to try - don't use like, use where. The email address has to be exact so like is unnecessary.
Second - do you have indexes on the fields you are searching?

https://codeigniter.com/user_guide/datab...ffered_row

PHP Code:

$this->db->like('email', $input_string); 


First thing to try - don't use like, use where. The email address has to be exact so like is unnecessary.
Second - do you have indexes on the fields you are searching?

It returns empty with like or where.
And yeah the table has a Primary index (id)

Code:

if( $type == 'email' )
{
    $sql = "SELECT * FROM members WHERE email = ?";
    $query = $this->db->query( $sql, array( $input_string ) );

    // If email address is not unique
    if( $query->num_rows() > 1 )
    {
        echo 'More than one result:<br />';
        echo '<pre>';
        print_r( $query->result_array() );
        echo '</pre>';
    }

    // If email address is unique
    else if( $query->num_rows() == 1 )
    {
        echo 'Just one result:<br />';
        echo '<pre>';
        print_r( $query->row_array() );
        echo '</pre>';
    }

    echo 'No results';
}

Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3

I have never had problems with CI so far, but yesterday I started a project in which I have to handle big amounts of data, the database has approximately 10m rows on several tables and I'm struggling with a simple search function


My controller looks something like this. Its need to retrieve the username of the user by entering the email, ID or viceversa:

PHP Code:

public function username_finder() {

  $type = $this->input->post('type');

  if ($type == 'email') {
    $this->form_validation->set_rules('input_string', 'Input String', 'trim|required|valid_email');
    if($this->form_validation->run() == FALSE) { } else {

      $input_string = $this->input->post('input_string');
      $result = $this->user_model->finder_engine($input_string, $type);
      return $result;
      }
   }
} 


The finder_engine on my model:

PHP Code:

public function finder_engine($input_string, $type) {


  if ($type == 'email') {
    $this->db->like('email', $input_string);
    $query = $this->db->get('members');
    return $query->result();
   }
} 


My members table looks like this:
id  | username | email | id_number | password

When I run this on the members table (10m rows) it returns and empty string.

I have tried with different versions but it always returns an empty array:

Code:

if ($type == 'email') {

   $query = "SELECT * FROM members WHERE email=?";
   $query = $this->db->query($query, array($input_string));
   return $query->result_array();

 }

I duplicated the table and cut it off to 25000 rows, still empty, but when I run the same queries on the same table with 5000 records it returns the values perfectly. I guess this is a problem with the memory or with the execution time, but I have no idea. How can I approach this properly? What are good measures for CI regarding handling this amount of data?

Thanks a lot in advance!

I don't see any wrong on how you use the query builder. I think it is your input. Try to echo out the $this->db->last_query() after the execution to make visible to you the query that have been executed and try to play/experiment this in your Database IDE (e.g phpmyadmin).

Also, try to echo out the value of $this->input->post('type'). There is a possibility that you just missed out the name of the field. Its value is very important in your script since you have to check if it is "email" at the first place.

[Just a programmer] [/Just a programmer]

It returns empty with like or where.
And yeah the table has a Primary index (id)

1) You also can put an index on individual fields in your database table. Makes a HUGE difference for searching. Don't put an index on all the fields, just the email field that you are searching on.

2) Do not use Like. Use Where to take full advantage of the index.

Change maximum execution time in xampp/phpmyadmin/libraries/config_default.php
$cfg['ExecTimeLimit'] = 300; to $cfg['ExecTimeLimit'] = 0;
/**
* maximum execution time in seconds (0 for no limit)
*
* @global integer $cfg['ExecTimeLimit']
*/
$cfg['ExecTimeLimit'] = 0;

It returns empty with like or where.
And yeah the table has a Primary index (id)

1) You also can put an index on individual fields in your database table. Makes a HUGE difference for searching. Don't put an index on all the fields, just the email field that you are searching on.

2) Do not use Like. Use Where to take full advantage of the index.

Thanks! solved it thanks to all the suggestions. Biggest problem was internally in the character set of the tables!

Is incredible how fast CI can process a search like this, never disappoints