Welcome Guest, Not a member yet? Register   Sign In
[Feature] Database table size estimation
#1

When paginating an API for example, it is often crucial to know how many pages you have (or how many rows you have) so you will show the correct amount of pages, show infinite scrolling when needed, etc..

The simplest, DB engine agnostic solution would be:
select("COUNT(1) as c")->from("table")->where()->where()....->where()->group()
(No order, no limit)

This way is very naive, and can take alot of time sometimes.

Feature request:
DB to have:
$this->db->estimate();


Simplest? will execute the prepared query, with no limit and no order
Better? 
- MySQL has an efficient way of calculating, selecting `SQL_CALC_FOUND_ROWS`
- Postgre has it's own solution
and I guess every DB has it's own.

So the flow would be something like:
$this->db->select("something")->from("somewhere")->where("some", "value")->order_by('key', 'asc')->limit(10, 10)->estimate();
$query = $this->db->get();
$result = $query->result();
$estimation = $query->estimation();


I think it is very functional.

If my explaination is not great, please et me know how to fix it Smile
Reply
Reply
#3

(10-27-2016, 08:42 PM)enlivenapp Wrote: What's wrong with these?

http://www.codeigniter.com/user_guide/da...r-database

http://www.codeigniter.com/user_guide/da...er-methods

These are great methods, but not exactly what is needed for pagination.
Lets say I have this query:
SELECT * FROM users WHERE geneder = "male" LIMIT 0, 50
`Num rows` will return 50 rows.
`Count all` will return 1000 rows
While the result I want is 495. Why? because I have 495 males, and 505 females in the table.

The simple solution I said would be:
SELECT COUNT(1) FROM users WHERE geneder = "male"
Which will return 495, and is very slow.

The MySQL better solution would be:
SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE geneder = "male" LIMIT 0, 50
Which will return 50 rows.
Then:
SELECT FOUND_ROWS() as count
would return 495 (number, not rows)
Reply
#4

So use $this->db->query('');
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

Hi,

I think you should do a bit of testing.
I ran both type of queries a few times on a 7.3 millions records table.
Also restarted the server to make sure the result was not due to caching

Code:
SELECT SQL_CALC_FOUND_ROWS * FROM MASTER WHERE Gender = "M" LIMIT 0, 50;
SELECT FOUND_ROWS() AS counted;

That took : avg. 5.375 seconds to execute


Code:
SELECT  * FROM `master` WHERE Gender = "M" LIMIT 0, 50;
SELECT COUNT(Id) AS counted FROM `master` WHERE Gender = "M";

That took : avg: 2.363 seconds to execute
A good decision is based on knowledge and not on numbers. - Plato

Reply
#6

(10-28-2016, 03:44 AM)InsiteFX Wrote: So use $this->db->query('');
But this is difference between DB drivers, and I am hoping I can make it run on at least MySQL and Postgres

(10-28-2016, 05:47 AM)salain Wrote: Hi,

I think you should do a bit of testing.
I ran both type of queries a few times on a 7.3 millions records table.
Also restarted the server to make sure the result was not due to caching

Code:
SELECT SQL_CALC_FOUND_ROWS * FROM MASTER WHERE Gender = "M" LIMIT 0, 50;
SELECT FOUND_ROWS() AS counted;

That took : avg. 5.375 seconds to execute


Code:
SELECT  * FROM `master` WHERE Gender = "M" LIMIT 0, 50;
SELECT COUNT(Id) AS counted FROM `master` WHERE Gender = "M";

That took : avg: 2.363 seconds to execute

Thanks for taking the time to do this
It is very surprising to me, because I did try the same just now on a 150K table, with 6 joins, a couple of wheres, and one group, and it took ~8 and ~13 seconds respectively. Maybe because my query is a lot bigger, but IDK. I will research this more deeply, because apparently my premise is wrong.
Cant share the query, I don't think I am allowed to share my work contents

However! I just did my own none-work testings for this, and figured out that even not related to time, doing a "COUNT" is more complex and confusing.
Example:
Code:
SELECT SQL_CALC_FOUND_ROWS * FROM clients
JOIN accounts ON clients.client_id = accounts.client_id
WHERE status = 1
GROUP BY clients.client_id
LIMIT 0, 10;

SELECT FOUND_ROWS() AS counted;

SELECT COUNT(*) FROM clients
JOIN accounts ON clients.client_id = accounts.client_id
WHERE status = 1
GROUP BY clients.client_id;
The third query returns a list of countings of "accounts" belonging to the "clients". Not a number of results..
So selecting a "COUNT(1)" is not a global solution.


Will keep updating when I have a result on why time is inconsistent
Reply
#7

If you want a faster query then index the query fields.
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply




Theme © iAndrew 2016 - Forum software by © MyBB