Is there a better way to count query results?

[eluser]mhulse[/eluser]
Hi,

In my model, I have this bit of code to check if an entry exists:

Code:

private function check_entry()
{
    $this->db->select('COUNT(*) AS total_count, `container`, `margin`', FALSE);
    $this->db->where('container = ' . $this->container);
    $this->db->where('margin = ' . $this->margin);
    $query = $this->db->get($this->table);
    $result = $query->row();
    return ($result->total_count > 0) ? TRUE : FALSE;
}

And before doing an insert, I call the above method:

Code:

if ( ! $this->check_entry()) {
    $this->db->insert(...);
}

It works, but I am wondering if there is an easier, more efficient, way to make sure an entry is unique before doing an insert?

Sorry if noob question, I am just now learning a bunch of new stuff pertaining to mysql and CI.

Thanks!
Micky

[eluser]Joshua Logsdon[/eluser]
Avoiding the count altogether, you could also search for one matching record... so something like:
SELECT 1 WHERE container = 'myc1' AND margin = 'mym1' LIMIT 1
and if a row was returned an entry exists

To get detailed though I'm not a guru, if it's a MyISAM table then COUNT(*) will be already stored, which is good, BUT I doubt 'container' and 'margin' are indexes so that takes you back down to average. If it's a small table then really any changes you do for efficiency may be negligible.

I guess my point for thought is if you don't need to know how many exist, you can just find if one exists.

[eluser]John_Betong[/eluser]
 
Try CodeIgniter's Profiler utility with and without indexes. I was very surprised at the speed increase.
 
 
 

[eluser]mhulse[/eluser]
Hi Joshua! Many thanks for the reply, I really appreciate it.

[quote author="Joshua Logsdon" date="1264332443"]Avoiding the count altogether, you could also search for one matching record... so something like:
SELECT 1 WHERE container = 'myc1' AND margin = 'mym1' LIMIT 1
and if a row was returned an entry exists[/quote]

Ahhh, interesting! Based upon that suggestion, here is my latest code:

Code:

private function check_entry()
{
    $this->db->select('1 `container`, `margin`', FALSE);
    $this->db->where('container = ' . $this->container);
    $this->db->where('margin = ' . $this->margin);
    $this->db->limit(1);
    $query = $this->db->get($this->table);
    return ($query->num_rows() > 0) ? TRUE : FALSE;
}

... it appears to work!


[quote author="Joshua Logsdon" date="1264332443"]To get detailed though I'm not a guru, if it's a MyISAM table then COUNT(*) will be already stored, which is good, BUT I doubt 'container' and 'margin' are indexes so that takes you back down to average. If it's a small table then really any changes you do for efficiency may be negligible.[/quote]

Ah, thanks for the details.

I am pretty new to working with mysql from the ground up, but when I created the table I set 'container' and 'margin' as primary keys:

Code:

CREATE TABLE grid_data(
    container INT NOT NULL,
    margin INT NOT NULL,
    data TEXT NOT NULL,
    tm timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY(container, margin)
)

Both 'container' and 'margin', when put together, have to be unique... I think this is called a "composite primary key"? Feel free to give feedback on this though... I am just learning all this stuff.

[quote author="Joshua Logsdon" date="1264332443"]I guess my point for thought is if you don't need to know how many exist, you can just find if one exists.[/quote]

Makes sense to me!

Thanks a billion Joshua!

Cheers,
Micky

[eluser]mhulse[/eluser]
[quote author="John_Betong" date="1264337568"] 
Try CodeIgniter's Profiler utility with and without indexes. I was very surprised at the speed increase.
 
 
 [/quote]

Thank you! I will take a look at the Profiler.

Again, I am pretty new to CI and mysql (long time EE/PHP user though), so I need all the help I can get.

[eluser]Joshua Logsdon[/eluser]
Hi mhulse (Micky)! No problem. Yeah, the Profiler is one way to get some overall performance info.

I wanted to make a quick note that (also you would want a comma after 1):
$this->db->select('1, `container`, `margin`', FALSE);
could also just be:
$this->db->select('1', FALSE);
and that keeps your query from returning data/using bandwidth that isn't necessary.

Again, not a guru here, but if you are looking for every corner to cut, just because columns are in the WHERE clause doesn't mean they have to be in the SELECT clause. The "SELECT 1" is just to return the number 1... it could be "SELECT 'i just want to know if something exists'" to return that string if you wanted... anything just so long as the query returns something. The more you return the more bandwidth is used though... again, this may be completely negligible for your performance but is a thought.

Being new to mysql, this probably falls under the "too much information" category but at least you know it's out there..... if you are using a tool like phpMyAdmin it also has some query profiling ability, as in not the whole app and just the query. Or if just using MySQL you could run the query with EXPLAIN (http://dev.mysql.com/doc/refman/5.0/en/explain.html) and/or profiling (http://dev.mysql.com/tech-resources/arti...filer.html). Using tools like this you could compare queries, make sure indexes are used, etc.

[eluser]mhulse[/eluser]
[quote author="Joshua Logsdon" date="1264360588"]I wanted to make a quick note that (also you would want a comma after 1):
$this->db->select('1, `container`, `margin`', FALSE);
could also just be:
$this->db->select('1', FALSE);
and that keeps your query from returning data/using bandwidth that isn't necessary.

Again, not a guru here, but if you are looking for every corner to cut, just because columns are in the WHERE clause doesn't mean they have to be in the SELECT clause. The "SELECT 1" is just to return the number 1... it could be "SELECT 'i just want to know if something exists'" to return that string if you wanted... anything just so long as the query returns something. The more you return the more bandwidth is used though... again, this may be completely negligible for your performance but is a thought.[/quote]

WOW! Great tips! Thank you Joshua!!!

I definitely like the idea of using $this->db->select('1', FALSE);.

And thank you for the clarification/details.

Being new to mysql, this probably falls under the "too much information" category but at least you know it's out there.....

I definitely needed the detailed explanation. I am looking to learn as much as possible about CI/MVC/Models/MySql/related, so I really appreciate you taking the time to help explain things to me.

if you are using a tool like phpMyAdmin it also has some query profiling ability, as in not the whole app and just the query. Or if just using MySQL you could run the query with EXPLAIN (http://dev.mysql.com/doc/refman/5.0/en/explain.html) and/or profiling (http://dev.mysql.com/tech-resources/arti...filer.html). Using tools like this you could compare queries, make sure indexes are used, etc.

More great info!! Thanks again Joshua, I owe you one!

Have a great day!
Cheers,
Micky

[eluser]Unknown[/eluser]
hey that was nice to have tips from you...even i too had a similar query and i hope tips given here can work out...

[eluser]sandeep nami[/eluser]
u can use coun(*) function of data base

[eluser]mhulse[/eluser]
[quote author="sandeep nami" date="1264426238"]to know the number of results u can use
$this->db->count_all_results($this->table)[/quote]

Hi!

I thought that was for the whole table. Will that count results based on my select and where clauses?

Also, here is my latest code (php5 chaining):

Code:

private function check_entry()
{
    $query = $this->db->select('1', FALSE)->where('container = ' . $this->container)->where('margin = ' . $this->margin)->limit(1)->get($this->table);
    return ($query->num_rows() > 0) ? TRUE : FALSE;
}

Thanks!
Micky