MySQL Preference

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
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

MySQL Preference

Post by s.dot »

Which of the following queries would mysql do better/faster/more efficiently?

Code: Select all

$result = mysql_query("
	SELECT
		*
	FROM
		`users`
	WHERE
		`username` = '$u'
	AND
		`activated` = 1
	LIMIT 1
") or die(mysql_error());
or

Code: Select all

$result = mysql_query("
        SELECT
	`49`,`different`,`fields`
        FROM
		`users`
	WHERE
		`username` = '$u'
	AND
		`activated` = 1
	LIMIT 1
") or die(mysql_error());
I have a table width 73 fields. The users table (pretty heavily accessed). Would mysql be better selecing *, or individually selecting 49 fields?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

In the case of selecting all the fields, I think select * from table or select field1, field2 .. will have same performance .
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Well obviously it would be better on memory to select 49 of the 73 fields. But speed wise, would select * be faster since it doesn't (im assuming) have to gather each of the 49 specified fields, but rather it would grab all 73 at once?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Re: MySQL Preference

Post by raghavan20 »

scottayy wrote:Which of the following queries would mysql do better/faster/more efficiently?

Code: Select all

$result = mysql_query("
	SELECT
		*
	FROM
		`users`
	WHERE
		`username` = '$u'
	AND
		`activated` = 1
	LIMIT 1
") or die(mysql_error());
or

Code: Select all

$result = mysql_query("
        SELECT
	`49`,`different`,`fields`
        FROM
		`users`
	WHERE
		`username` = '$u'
	AND
		`activated` = 1
	LIMIT 1
") or die(mysql_error());
I have a table width 73 fields. The users table (pretty heavily accessed). Would mysql be better selecing *, or individually selecting 49 fields?

i think select * would be faster, since it does not even have to take time to search for columns. The problem would be only when you want to join with other tables. The select * query is faster but at the same time more queries like this occupies server memory and finally may be more queries have to wait to be executed.

It is always better to select only the fields we need at any time
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Selecting the individual fields is generally faster when your not selecting all of them as it helps MySQL decide what indexes to use. It also uses less memory.
Post Reply