Is this bad sql to count total for pagination? (CI3-dev)

[eluser]a_h_abid[/eluser]
I need some kind of review on the SQL I usually run to count total rows for pagination.

Because I mostly use MySQL for my apps, to count all data, I simply remove the limit portion from the sql then run it and use num_rows() to get the total count.

Code:

$sql = $this->countries_model
->order_by('name')
->limit($limit)
->offset($offset)
->get_sql();
$count = $this->db->query(preg_replace('/LIMIT.+$/i','', $sql))->num_rows();
$items = $this->db->query($sql)->result();

I use custom MY_Model which helps me do some special stuffs like getting the sql as string.

Mainly I want to know here how much will it affect on a db table with million rows of data? If it is, what would be the best way to do?

[eluser]treenef[/eluser]
You should always optimize your queries if you CAN by doing a limit. An example, would be selecting a password for a userid. Do a limit(1) as there should only be one row returned.

Additionally, avoid using $this->db->select('*') where you can.

Unfortunately, if you don't know how many rows are to be returned you can't do much about it, as if you try and limit your query you might miss important data.

Hope that helped.

[eluser]joergy[/eluser]
ever thought about
select count(id) from...
or similar, if You expect many records?

IMHO this
$this->db->query(preg_replace('/LIMIT.+$/i','', $sql))->num_rows();
isn't easy to read. Why not splitting...

[eluser]www.sblog.in[/eluser]
If you have millions of records then check for paging optimization like http://www.databaseskill.com/1159218/

[eluser]a_h_abid[/eluser]
[quote author="joergy" date="1403472148"]ever thought about
select count(id) from...
or similar, if You expect many records?

IMHO this
$this->db->query(preg_replace('/LIMIT.+$/i','', $sql))->num_rows();
isn't easy to read. Why not splitting...[/quote]

Right now wrote like this so you guys can understand my current process of getting total rows record.

I do use count('id') for simple queries, but sometimes i have complicated queries that may have subqueries, union etc. within it. In those case using it doesn't produces the required result.

[quote author="www.sblog.in" date="1403516578"]If you have millions of records then check for paging optimization like http://www.databaseskill.com/1159218/[/quote]

Thanks for link. But I can't seems to understand the english he written there. Is he talking about setting index keys?