Welcome Guest, Not a member yet? Register   Sign In
My query returns empty results with big tables. Any advice?

(This post was last modified: 09-14-2016, 09:06 PM by Joel Catantan.)

(09-14-2016, 02:08 PM)HeyDarling Wrote: 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') {
    $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:
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] Cool [/Just a programmer]

Messages In This Thread
RE: My query returns empty results with big tables. Any advice? - by Joel Catantan - 09-14-2016, 09:04 PM

Theme © iAndrew 2016 - Forum software by © MyBB