This query is meant to return a row based on the conditions in the WHERE clause. Then I would count the rows returned in the result set and do what's necessary. In other words this query is for checking if there is a specific row in the database, so it doesn't matter what fields or data is returned. So which is the most fast or least intense query of the above; I don't want to bog my MySQL server?
The wildcard * selects all the columns from the row, which takes up more memory and in some cases (TEXT/BLOB fields) requires an extra IO operation. If you don't need all the columns, select just what you need.
pytrin wrote:If you don't need all the columns, select just what you need.
I don't need any columns, just a row to say that there is a user, is a something, is a whatever, etc. It's just so I can check to see if something exists, and if not I could add that something in another query.
SELECT COUNT(`field`) FROM `table` WHERE `field` = 'foo';
Don't pull out a resultset full of 1s if you're going to count them, just pull out a resultset with the number of rows in the first and only row. Not sure if it'll be faster but it'll save memory.
SELECT COUNT(`field`) FROM `table` WHERE `field` = 'foo';
Don't pull out a resultset full of 1s if you're going to count them, just pull out a resultset with the number of rows in the first and only row. Not sure if it'll be faster but it'll save memory.
Well I'm only expecting one row. But I whenever I need to count the rows returned I will do it with the COUNT function, like you said. But in order for the COUNT function to work, do I need the GROUP BY clause? I recall COUNT being an aggregate function.
pytrin wrote:COUNT is a special kind of aggregate function, it can be used without a group by clause.
Oh I didn't know that. What is the difference between when I use the COUNT function with the GROUP BY clause and when I use the COUNT function without the GROUP BY clause?