[Feature] Database table size estimation |
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
(10-27-2016, 08:42 PM)enlivenapp Wrote: What's wrong with these? 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)
So use $this->db->query('');
What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
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; That took : avg. 5.375 seconds to execute Code: SELECT * FROM `master` WHERE Gender = "M" LIMIT 0, 50; That took : avg: 2.363 seconds to execute A good decision is based on knowledge and not on numbers. - Plato
(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, 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 So selecting a "COUNT(1)" is not a global solution. Will keep updating when I have a result on why time is inconsistent
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 )
|
Welcome Guest, Not a member yet? Register Sign In |