Why does paginate call the database twice?

[eluser]gh0st[/eluser]
I'm trying to build a search form, which will query the database and then eventually spit the results out in a paginated form.

I've gone through the tutorial, and the forum examples (where possible) and every time the Paginate class appears to call the database twice.

1. To run your query (with no MySQL limit) and find out how many records there are.
2. To do your query (with MySQL limit) and then display them.

What if your database is really big, such as 5-10MB+ databases?

Firstly, should I be concerned that the paginate calls the database twice, and secondly; is the paginate class that comes with CI the best paginate class to use?

Example;

Code:

// this is an example and may not run correctly
// controller
class Paging extends Controller
{
    function Paging()
    {
        parent::Controller();
        $this->output->enable_profiler(TRUE);
        $this->load->database();
        $this->load->library('pagination');
    }

    function index()
    {
        // build query
        $sql = 'SELECT id, name FROM `books` ORDER BY name DESC';

        // run first query
        $query        = $this->db->query($sql);
        $num_rows    = $query->num_rows();

        // get offset
        $offset        = $this->uri->segment(3);

        // pagination config
        $config['base_url']    = '/paging/index/';
        $config['total_rows']    = $num_rows;
        $config['cur_page']    = $offset;
        $config['per_page']    = 10;

        // run second query
        $sql_second        = $sql ." LIMIT ".$config['cur_page'].", ".$config['per_page'];
        $data['results']    = $this->db->query($sql_second);

        // debug output
        print '<p>'.$sql_second.'</p>';

        // initalize pagination
        $this->pagination->initialize($config);

        // load the HTML Table Class
        $this->load->library('table');
        $this->table->set_heading('id', 'name');

        // load view
        $this->load->view('paging', $data);
    }

} // end class

[eluser]m4rw3r[/eluser]
Use this as the count code, only an int is received:

Code:

$num = array_shift($this->db->query('SELECT COUNT(1) FROM `books`')->row_array());

[eluser]gh0st[/eluser]
I thought you had to do the query (regardless of complexity) twice to get the right number of rows back?

Let's say you had a complex query involving some INNER JOIN's... don't you have to do a num_rows on the INNER JOINed query so that you get the correct number of records?

Or.. perhaps you have a complex, filtered query (with no INNER JOINs, etc), don't you still need to do a num_rows on the complex query, so that you get the correct number of records?

Thanks

[eluser]m4rw3r[/eluser]
Yeah, you do need to query the database twice.
But it is very unnecessary to fetch the data, hence the COUNT(1) instead.

COUNT(1) will count all rows (replaces the row with a 1, making it unnecessary to read the real data), and return the result as one row (therefore I use array_shift() with row_array()).
So what you do is the following (this is the basics of doing a count query, also works with most joined queries too):

Code:

// take the original query
SELECT id, name FROM `books` ORDER BY name DESC
// replace the selected columns with COUNT(1)
SELECT COUNT(1) FROM `books` ORDER BY name DESC
// remove the order by, and you have your count query:
SELECT COUNT(1) FROM `books`

// example with a JOIN:
SELECT users.*, groups.name AS group_name
FROM users
JOIN groups ON users.group_id = groups.id
WHERE group.name = 'somegroup'
// count:
SELECT COUNT(1)
FROM users
JOIN groups ON users.group_id = groups.id
WHERE group.name = 'somegroup'

[eluser]gh0st[/eluser]
Thanks for the illustrations, that helped a lot!

I'm going to test it out!

Thanks

[eluser]madeks[/eluser]
Hi,

I'm don't mean to dig the death here but as I got the same problem with "gh0st" and he seem to disappeared.

It's definite thanks to "m4rw3r", the solution is worked!!!.

I have quite similar problem. I got 20k row with more than 70 fields from DB to do some stat calculation.
It use 90 MB only for this processing. No doubt it is out of memory on my local.

After this thread, I found one stupid mistake in my code.
As you can guess we don't use all fields to do the stat calculation.
So I change it to select only the fields that need a calculation.

The result is beyond good. The memory usage is reduce to only 15MB.

So I decide to share it here in case someone got the same problem as mine.

[eluser]Evil Wizard[/eluser]
I personally run the query to get the results I want (with a limit) then the very next query is

Code:

SELECT FOUND_ROWS() AS FoundRows;

[eluser]Evil Wizard[/eluser]
This also works on any type of query that produces a resultset in MySQL

Code:

SHOW TABLES;
SELECT FOUND_ROWS() AS FoundRows;

paste that straight into your phpmyadmin and it will give you a recordset with one field called FoundRows with the total number of tables in your current database.

This will work with the joins too

Code:

// example with a JOIN:
SELECT users.*, groups.name AS group_name
FROM users
JOIN groups ON users.group_id = groups.id
WHERE group.name = 'somegroup'
// count:
SELECT FOUND_ROWS() AS FoundRows;

The query doesn't need rewriting it is usable in conjunction with the previous query.

[eluser]madeks[/eluser]
Thanks "Wizard", this function rock it.

I go to MySQL site for the description and seem it is support from version 3 onward.

Anyway, I wonder is there are a similar function like this by other DBMS?
As we need to aware when it come to other DBMS