am I doing unnecessary operations by mysql_fetch_array?

table `user`
---------------------------------------
| id | user_name | gender |
---------------------------------------

I can gurantee that ids in table user are unique (so each number
correspond at most one row in the table). I have two questions
1) can I somehow do away with mysql_fetch_array($result);?
2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
'$id')"; to replace line (1), do I get some better performance
theoretically? if so, can I simply return $row; ---because
'user_name' is the only field being selected.

$sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
$result = mysql_query($sql_query);
$row = mysql_fetch_array($result);
return $row['user_name'];

Thanks,

Re: am I doing unnecessary operations by mysql_fetch_array?

On 02.08.2007 07:51 newbie wrote:
> table `user`
> ---------------------------------------
> | id | user_name | gender |
> ---------------------------------------
>
> I can gurantee that ids in table user are unique (so each number
> correspond at most one row in the table). I have two questions
> 1) can I somehow do away with mysql_fetch_array($result);?

Yes, if you need one single field, you can use mysql_fetch_field. The
performance benefit would be negligible though.

> 2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
> '$id')"; to replace line (1), do I get some better performance

Yes, of course. Some say '*' is harmful, always list the fields you want.

> theoretically? if so, can I simply return $row; ---because
> 'user_name' is the only field being selected.
>
> $sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
> $result = mysql_query($sql_query);
> $row = mysql_fetch_array($result);
> return $row['user_name'];
>
> Thanks,
>



--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

Re: am I doing unnecessary operations by mysql_fetch_array?

..oO(newbie)

>table `user`
>---------------------------------------
>| id | user_name | gender |
>---------------------------------------
>
>I can gurantee that ids in table user are unique (so each number
>correspond at most one row in the table). I have two questions
>1) can I somehow do away with mysql_fetch_array($result);?
>2) If i use $sql_query = "SELECT user_name from `user` WHERE (id =
>'$id')";

Don't quote $id, it's a numeric value.

>to replace line (1), do I get some better performance
>theoretically?

You need both, a mysql_query() call to query the database and a
mysql_fetch_*() call to get the results. But you could use the second
paramater of mysql_fetch_array() to specify whether it should return the
result as a numeric or associative array. By default it does both.

>if so, can I simply return $row; ---because
>'user_name' is the only field being selected.
>
> $sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)

You shouldn't use "SELECT *" in production code, list all columns
explicitly.

Micha

Re: am I doing unnecessary operations by mysql_fetch_array?

On 2 Aug, 10:04, Michael Fesser wrote:

> >if so, can I simply return $row; ---because
> >'user_name' is the only field being selected.
>
> > $sql_query = "SELECT * from `user` WHERE (id = '$id')"; // (1)
>
> You shouldn't use "SELECT *" in production code, list all columns
> explicitly.
>
> Micha

You shouldn't use "SELECT *" in production code IF YOU CAN list all
columns
explicitly.

....reason being that you're loading more data into the mysql buffer
than you need, then it gets copied into the PHP variable space.

> (1), do I get some better performance theoretically?

Unless someone shoves a LOB column type and populates it, you're
unlikely to see much of a performance difference though.

C.

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

..oO(David Gillen)

>If I want all column in a table though surely SELECT * is better.

Depends.

>In fact, performance on SELECT * is better than "SELECT field1, field2, field3,
>field4, field5, field6, field7, field8" because mysql doesn't have to go
>looking for the specific fields you've requested

Did you test that or is it just a wild guess?

>but just returns you
>everything and since you know you want everything there is no problem.

You shouldn't do the work of the optimizer, it knows better how to
optimize a query in the most efficient way. There are at least two
problems with SELECT * :

1) It might return a lot of unnecessary columns, especially when doing
complicated JOINs.

2) The order in which the fields are returned might not always be the
same. If your application depends on that (there are situations where
this can be an issue), then it might fail at some time.

Don't you think there's a reason why the MySQL team itself warns that
you should _never_ rely on using SELECT *, except maybe for testing or
debugging purposes?

Micha

Re: am I doing unnecessary operations by mysql_fetch_array?

On 3 Aug, 09:37, David Gillen wrote:
> C. said:> On 2 Aug, 10:04, Michael Fesser wrote:
> >> You shouldn't use "SELECT *" in production code, list all columns
> >> explicitly.
>
> >> Micha
>
> > You shouldn't use "SELECT *" in production code IF YOU CAN list all
> > columns
> > explicitly.
>
> > ...reason being that you're loading more data into the mysql buffer
> > than you need, then it gets copied into the PHP variable space.
>
> If I want all column in a table though surely SELECT * is better.
> In fact, performance on SELECT * is better than "SELECT field1, field2, field3,
> field4, field5, field6, field7, field8" because mysql doesn't have to go
> looking for the specific fields you've requested but just returns you
> everything and since you know you want everything there is no problem.
>
> D.
> --
> Fermat was right.

As well as what Micha said, there is another potential drawback.

Suppose someone added a TEXT BLOB field containing megabytes of data
to the table.
Any queries that used * would nowbe bringing back all that data each
time.

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

On 03.08.2007 13:06 Michael Fesser wrote:
> .oO(David Gillen)
>
>> If I want all column in a table though surely SELECT * is better.
>
> Depends.

On what, if I may ask?

>
>> In fact, performance on SELECT * is better than "SELECT field1, field2, field3,
>> field4, field5, field6, field7, field8" because mysql doesn't have to go
>> looking for the specific fields you've requested
>
> Did you test that or is it just a wild guess?

I just did and can confirm. SELECT * was (slightly but) better on a
table with 6 cols and 1000 rows.

>
>> but just returns you
>> everything and since you know you want everything there is no problem.
>
> You shouldn't do the work of the optimizer, it knows better how to
> optimize a query in the most efficient way. There are at least two
> problems with SELECT * :
>
> 1) It might return a lot of unnecessary columns, especially when doing
> complicated JOINs.

This is not an issue if we need them all.

>
> 2) The order in which the fields are returned might not always be the
> same. If your application depends on that (there are situations where
> this can be an issue), then it might fail at some time.

If your application depends on that, it shouldn't. Refactor.

>
> Don't you think there's a reason why the MySQL team itself warns that
> you should _never_ rely on using SELECT *, except maybe for testing or
> debugging purposes?

Because they care about how fast their server is, not about how
extensible our applications are, I guess.


--
gosha bine

makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

..oO(David Gillen)

>2) vague rumbling about the mysql team advising against it, although I'm
>having difficulting finding that on the mysql site

http://dev.mysql.com/doc/refman/5.1/en/change-column-order.h tml

Micha

Re: am I doing unnecessary operations by mysql_fetch_array?

..oO(gosha bine)

>On 03.08.2007 13:06 Michael Fesser wrote:
>>
>> 2) The order in which the fields are returned might not always be the
>> same. If your application depends on that (there are situations where
>> this can be an issue), then it might fail at some time.
>
>If your application depends on that, it shouldn't. Refactor.

Not necessarily. Why do you should list all fields when doing an insert
into a table

INSERT INTO table (field1, field2, ...) VALUES (...)

instead of just doing a

INSERT INTO table VALUES (...)

?

Because order matters, and only the server knows in which order fields
are stored and retrieved. If you use some kind of abstraction layers or
a generic system to access database tables, there can be the situation
where you have to make sure that the returned record set contains the
retrieved fields in the order the processing component expects.
Sometimes you might be able to refactor that, sometimes it might be too
much work. Explicitly listing all columns in the SELECT statement works
always.

Micha

Re: am I doing unnecessary operations by mysql_fetch_array?

David Gillen wrote:
> Gary L Burnore said:
>> I'm thinking he's not having this discussion in comp.databases.mysql
>> because he knows what the response will be. He was hoping he wouldn't
>> get the same response here. :)
>
> The discussion is being had here because it follows on from a question asked.
> I've also yet to see a valid reason as to why select * shouldn't be used on a
> single table query when you want all fields from that table other than
> 1) you can't depend on the order of the fields return. Which is irrelevant if
> you write your code so that you need not be concerned with that.
> 2) vague rumbling about the mysql team advising against it, although I'm
> having difficulting finding that on the mysql site, and I'm guessing this
> really relates to complex joins and the order of returned results. Both of
> which do not affect the situation I posed.
>
> D.

Just because it's a follow on question doesn't mean it should be here.

You'll get much better answers in comp.databases.mysql - where all the
people are MySQL users, and some of the MySQL development team hang out.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

Post removed (X-No-Archive: yes)

Re: am I doing unnecessary operations by mysql_fetch_array?

David Gillen wrote:
> Michael Fesser said:
>> Explicitly listing all columns in the SELECT statement works
>> always.
>>
> So does SELECT * if you code for it.
>
> $sql = "SELECT * FROM table1";
> $query = mysql_query($sql);
> while($row = mysql_fetch_assoc($query))
> {
> echo "Always field 1: " . $row['field1'];
> }
>
> So now, where is the problem?
>
> D.

As I said earlier, the problem is when a TEXT BLOB or any other large field
is added to the database in future and then all * queries are pulling back
loads of data that is not required.

Argue all you like, but it is bad practice. I have come across lots of bad
programming which was fine for the original intention, but caused many
problems when the applications changed in later years. Had good practice
been followed there would have been no problem.

So you go and ignore all the good advice you want to and program as badly as
you wish. It's a fair bet that I'll never have to maintain your crap.

Re: am I doing unnecessary operations by mysql_fetch_array?

David Gillen wrote:
> Jerry Stuckle said:
>> David Gillen wrote:
>>> Gary L Burnore said:
>>>> I'm thinking he's not having this discussion in comp.databases.mysql
>>>> because he knows what the response will be. He was hoping he wouldn't
>>>> get the same response here. :)
>>> The discussion is being had here because it follows on from a question asked.
>>> I've also yet to see a valid reason as to why select * shouldn't be used on a
>>> single table query when you want all fields from that table other than
>>> 1) you can't depend on the order of the fields return. Which is irrelevant if
>>> you write your code so that you need not be concerned with that.
>>> 2) vague rumbling about the mysql team advising against it, although I'm
>>> having difficulting finding that on the mysql site, and I'm guessing this
>>> really relates to complex joins and the order of returned results. Both of
>>> which do not affect the situation I posed.
>>>
>>> D.
>> Just because it's a follow on question doesn't mean it should be here.
>>
>> You'll get much better answers in comp.databases.mysql - where all the
>> people are MySQL users, and some of the MySQL development team hang out.
>>
> I didn't pose the original question. Also it's peoples php code where the
> problem seems to lie, i.e. in the code being unable to handle changing of the
> order of the columns.
>
> D.

Nope, its ANY code, not just PHP.

And NOTHING in the last 20-30 posts in this thread have ANYTHING to do
with PHP. It's been all MySQL.

Please take this somewhere appropriate. It is no longer appropriate to
this newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: am I doing unnecessary operations by mysql_fetch_array?

On 4 Aug, 18:44, Jerry Stuckle wrote:
> David Gillen wrote:
> > Jerry Stuckle said:
> >> David Gillen wrote:
> >>> Gary L Burnore said:
> >>>> I'm thinking he's not having this discussion in comp.databases.mysql
> >>>> because he knows what the response will be. He was hoping he wouldn't
> >>>> get the same response here. :)
> >>> The discussion is being had here because it follows on from a question asked.
> >>> I've also yet to see a valid reason as to why select * shouldn't be used on a
> >>> single table query when you want all fields from that table other than
> >>> 1) you can't depend on the order of the fields return. Which is irrelevant if
> >>> you write your code so that you need not be concerned with that.
> >>> 2) vague rumbling about the mysql team advising against it, although I'm
> >>> having difficulting finding that on the mysql site, and I'm guessing this
> >>> really relates to complex joins and the order of returned results. Both of
> >>> which do not affect the situation I posed.
>
> >>> D.
> >> Just because it's a follow on question doesn't mean it should be here.
>
> >> You'll get much better answers in comp.databases.mysql - where all the
> >> people are MySQL users, and some of the MySQL development team hang out.
>
> > I didn't pose the original question. Also it's peoples php code where the
> > problem seems to lie, i.e. in the code being unable to handle changing of the
> > order of the columns.
>
> > D.
>
> Nope, its ANY code, not just PHP.
>
> And NOTHING in the last 20-30 posts in this thread have ANYTHING to do
> with PHP. It's been all MySQL.
>
> Please take this somewhere appropriate. It is no longer appropriate to
> this newsgroup.
>

I disagree - it's generically about programming - one thing I missed
in my previous post is that if there is a join it could give rise to
column naming being ambiguous (I don't have an env to hand to test it
- what happens if both tables have a column called 'description'?).
But this was outwith the scope of the original post.

As other people have hinted, using mysql_fetch_array (or any function
which assigns manufactured keys to an array) instead of
mysql_fetch_assoc (or any function which uses intrinsic keys) is just
plain bad programming.

Micha wrote:
> http://dev.mysql.com/doc/refman/5.1/en/change-column-order.h tml

relying on sequence within columns is not the way to solve the
problem. Nor can you rely on row sequence without explicit ordering in
the query.

C.