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.
Hi Joshua! Many thanks for the reply, I really appreciate it.
Joshua Logsdon - 24 January 2010 07:27 AM
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
Ahhh, interesting! Based upon that suggestion, here is my latest code:
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.
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:
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. :D
Joshua Logsdon - 24 January 2010 07:27 AM
I guess my point for thought is if you don’t need to know how many exist, you can just find if one exists.
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/articles/using-new-query-profiler.html). Using tools like this you could compare queries, make sure indexes are used, etc.
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.
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.
In Micky’s code he wants to know if a record exists, not necessarily how many records there are because in theory his code should only be allowing one anyway. He’s also looking for “better” ways to do it and is eager to learn so I’m trying to put the “why” behind answers so he can better understand and decide for himself.
1) Yes you can do this via COUNT(*). MyISAM caches the number of rows in the table so $this->db->count_all_results() would be the fastest. THAT’S IF you wanted how many total rows are in the table, i.e. no WHERE clause.
2) Because there is a WHERE clause, I believe MySQL has to perform a standard query then the aggregate COUNT() function. Because he is using a compound key, it will still be fast. A row will always be returned with the count.
3) With the SELECT statement, the standard query is still run but no aggregate function is run. Also we are trying to limit to 1 match, so one row will be returned IF one was found.
4) If you try benchmarking any of these queries (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html) you are going to find with a small result set that 2 & 3 are basically the same. If you have a huge result set, you should notice 3 start to be faster.
You can’t lose here Micky; what you were doing from the beginning would have kept on working for you. Just think of this as an exercise in SQL and then do what you feel is best or makes sense to you.
Hi Joshua! Thanks again for another very informative reply. Like I said before, I really appreciate you taking the time to share your pro knowledge and teach a noob a few new tricks. :D
Joshua Logsdon - 25 January 2010 03:52 PM
Hi guys, I promise this will be the last info from me because I know I’m “splitting hairs” at this point!
Oh, not splitting hairs… I think it is good to clarify.
In Micky’s code he wants to know if a record exists, not necessarily how many records there are because in theory his code should only be allowing one anyway. He’s also looking for “better” ways to do it and is eager to learn so I’m trying to put the “why” behind answers so he can better understand and decide for himself.
...<snip>...
You can’t lose here Micky; what you were doing from the beginning would have kept on working for you. Just think of this as an exercise in SQL and then do what you feel is best or makes sense to you.
Excellent advice and info!
I was planning on using all the advice you have given me. I have not had time to do any benchmarking, but I think the below query has been honed down to match all the great tips you have given me:
I have yet to test its speediness, but based on what we have talked about here (external links and all), it should be faster than my original count(*) approach.
Also, I like the thought of using PHP5 to chain, but it sure does kill the readability, and I just can’t seem to figure out how to format it nicely with line breaks and tabs! Hmm, maybe there is something in the CI style guide.
Thanks again Joshua! I have learned a lot based on your help here.
