Is there a better way to count query results?

[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