Which is a faster query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Which is a faster query?

Post by JellyFish »

Code: Select all

SELECT * FROM table WHERE FIELD = 'field';
or

Code: Select all

SELECT 1 FROM table WHERE FIELD = 'field';
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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Which is a faster query?

Post by Eran »

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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Which is a faster query?

Post by JellyFish »

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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Which is a faster query?

Post by jayshields »

Code: Select all

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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Which is a faster query?

Post by JellyFish »

jayshields wrote:

Code: Select all

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Which is a faster query?

Post by Eran »

COUNT is a special kind of aggregate function, it can be used without a group by clause.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: Which is a faster query?

Post by JellyFish »

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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Which is a faster query?

Post by Eran »

The difference is what gets counted - http://dev.mysql.com/doc/refman/5.1/en/ ... -rows.html
Post Reply