Number of Records returned in Database Query

[eluser]Fielder[/eluser]
There's an opportunity for the user to run a pretty big database query in their search criteria, which could return thousands of records (and subsequently take a while to query, execute, and render to the page).

Right now, I've limited the max. number of results returned to be rendered by counting the number of keys in the array.

Code:

if ($results['contracts']['count'] > $this->config->item('max_contractresults'))
            {
                echo "<li>Too many returned results ("
                    .$results['contracts']['count']
                    ."). Refine your search to less than "
                    .$this->config->item('max_contractresults').".</li>";
            }
            elseif ($results['contracts'])
            {
                $this->load->view('contractsearch_results',$results);
            }

But obviously this is after mySQL has run through the queries, and the records have already reached my PHP function. How can I limit the query search on the mySQL server side before the results even reach PHP?
By adding LIMIT to the statement, does this still require mySQL to go through the entire database, or will it stop querying once it finds 100 records?
I would like it to stop querying once it finds the number of results - the database could potentially have 500,000 records or more.

[eluser]brianw1975[/eluser]
this article might be of some use, or at least get you headed in the right direction.

http://www.arraystudio.com/as-workshop/m...limit.html

[eluser]gigas10[/eluser]
Finding 500,000 records will not take long anyway. The long part of it is rendering it to a page. Print and echo are terribly inefficient.

[eluser]Chad Fulton[/eluser]

Finding 500,000 records will not take long anyway. The long part of it is rendering it to a page. Print and echo are terribly inefficient.

This is only sort-of true (the specifics are inaccurate: print / echo are not "inefficient" functions, but users would see significant lag time if you attempted to print 500,000 records for two reasons: (1) Unless you chunked your data, the browser would first have to wait for PHP to create the entire document and then download it; and (2) browser rendering for 500,000 entries would be ridiculously slow).

On top of that, you don't want to be needlessly querying 500,000 records for each page load if that's not necessary.

Since you didn't really give us specifics about your application, it's kind of hard to speculate, but here are two thoughts which may help:

(1) If you want to limit a user to a certain number of rows, [/b]by all means use the LIMIT statement[/b]. It is optimized by MySQL and will stop the query from running after the given number of rows.

(2) If you're doing some sort of pagination scheme, you'll want to do 2 queries. First, you'll want to do a SELECT COUNT(*) ... query to figure out how many rows matched the query (this is vastly faster than selecting the entire row), and then you'll want to do a second query (with a LIMIT clause) to only select a few rows which the page will output.

Good luck!

[eluser]Fielder[/eluser]
Maybe the delay is not from the queries, but rather the rendering of the data table to the browser. The data flow is like this..

1. User selects search criteria in form, and presses the Search button
2. AJAX fires using the following jQuery code

Code:

$("#submit_contractlist").click(function() {
            con_id = "";
            con_number = $("#con_number").val();
            bus_name = $("#bus_name").val();
            query_status = "FALSE";
            
            if (con_number == '' && bus_name == '')
            {
                query_status = "FALSE";
            }
            else
            {
                query_status = "TRUE";
            }
            
            show_loading('Locating Contracts');
            
            $('#results').load(base_url + "all_search/AJAXsearchContract",{con_id: con_id, con_number: con_number, bus_name: bus_name, query_status: query_status},function(str)
            {
                
            });
        });

3. all_search/ajaxsearchcontract function fires and evaluates the number of returned results and if too many results returned it fails to $this->load->view().

Code:

function AJAXsearchContract()
    {
        $inputSchema = array('con_number' => '', 'bus_name' => '', 'con_id' => '');
        $inputData = array_intersect_key($_POST, $inputSchema);
        
        $query_status = $_POST['query_status'];

        //Throws error if no data was provided in any of the input fields
        if ($query_status == 'FALSE')
        {
            echo "<li>You must have data in at least 1 input field</li>";
        }
        else
        {
            $results['contracts'] = $this->contract->getContractsbyWhere($inputData);
            
            if ($results['contracts']['count'] > $this->config->item('max_contractresults'))
            {
                echo "<li>Too many returned results ("
                    .$results['contracts']['count']
                    ."). Refine your search to less than "
                    .$this->config->item('max_contractresults').".</li>";
            }
            elseif ($results['contracts'])
            {
                $this->load->view('contractsearch_results',$results);
            }
            else
            {
                echo "<li>No Matching Records</li>";
            }
        }
    }

4. mySQL statement is as follows inside the model function getContractsbyWhere()

Code:

function getContractsbyWhere($record_id)
    {
        $con_id = mysql_real_escape_string($record_id['con_id']);
        $con_number = mysql_real_escape_string($record_id['con_number']);
        $bus_name = mysql_real_escape_string($record_id['bus_name']);
        
        $this->db->select('*');
        $this->db->from('contract');
        $this->db->join('employees','employees.employee_rep = contract.employee_rep','left');
        $this->db->join('bus','bus.bus_id = contract.bus_id','left');
        $this->db->join('users','users.employee_id = employees.employee_id','left');
        
        if ($con_id != "")
            $this->db->where('contract.con_id',$con_id);
        if ($bus_name != "")
            $this->db->like('bus.bus_name',$bus_name);
        if ($con_number != "")
            $this->db->like('contract.

        $query = $this->db->get();
        if ($query->num_rows() > 0)
        {
            $results = array(
                'data' => $query->result_array(),
                'count' => $query->num_rows()
            );
            return $results;
        }
    }

5. And the results of the query are displayed in the view containing an ID results. Basically the view generated is dumped into the Results ID on the webbrowser and displayed to the user.

6. In the contractsearch_results view, it is running through a foreach loop and echoing the required fields.

[eluser]Chad Fulton[/eluser]
You may want to try something like this:

In your getContractsbyWhere function, do this:

Code:

function getContractsbyWhere($record_id)
    {
        $con_id = mysql_real_escape_string($record_id['con_id']);
        $con_number = mysql_real_escape_string($record_id['con_number']);
        $bus_name = mysql_real_escape_string($record_id['bus_name']);

        // First check the number of rows
        $this->db->count_all_results();
        
        $this->db->start_cache();
        $this->db->from('contract');
        $this->db->join('employees','employees.employee_rep = contract.employee_rep','left');
        $this->db->join('bus','bus.bus_id = contract.bus_id','left');
        $this->db->join('users','users.employee_id = employees.employee_id','left');

        if ($con_id != "")
            $this->db->where('contract.con_id',$con_id);
        if ($bus_name != "")
            $this->db->like('bus.bus_name',$bus_name);
        if ($con_number != "")
            $this->db->like('contract.'); // don't know what this line was supposed to be

        $this->db->end_cache();

        $count = $this->db->count_all_results();

        if($count > $this->config->item('max_contractresults')) {
                $results = array(
                        'data' => array(),
                        'count' => $count
                    );
                   return $results;
        }
        else {
                $this->db->select('*');
                $query = $this->db->get();
                if ($query->num_rows() > 0)
                {
                    $results = array(
                        'data' => $query->result_array(),
                        'count' => $query->num_rows()
                    );
                   return $results;
                }
        }
    }

That's a start anyway, and will vastly improve your performance, since it won't be querying all those rows when too many are returned.

You may also want to consider some sort of pagination scheme.

[eluser]danmontgomery[/eluser]
http://ellislab.com/codeigniter/user-gui...ecord.html

$this->db->limit();

Lets you limit the number of rows you would like returned by the query:
$this->db->limit(10);

// Produces: LIMIT 10

The second parameter lets you set a result offset.
$this->db->limit(10, 20);

// Produces: LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)

?

You can also use SQL_CALC_ROWS and FOUND_ROWS() in MySQL to get the number of rows that would have been returned without a LIMIT statement.

[eluser]wowdezign[/eluser]
It sounds like something I use both LIMIT ?,? in the query and pagination on the server for.

You'll need to add some stuff to your page(s), but it will be faster I think.