Page 1 of 1
MySQL Preference
Posted: Thu May 18, 2006 1:09 am
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?
Posted: Thu May 18, 2006 1:16 am
by dibyendrah
In the case of selecting all the fields, I think select * from table or select field1, field2 .. will have same performance .
Posted: Thu May 18, 2006 1:30 am
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?
Re: MySQL Preference
Posted: Thu May 18, 2006 2:35 am
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
Posted: Thu May 18, 2006 2:49 am
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.