is this better way to do it?

jasondavis

$sql ="select auto_id,subject,submit_date,user_id from friend_bulletin where user_id='$info[auto_id]' or user_id in ($str_friend_ids) order by submit_date DESC limit 0,5";

$sql ="select * from friend_bulletin where user_id='$info[auto_id]' or user_id in ($str_friend_ids) order by submit_date DESC limit 0,5";

which method is better for a very large DB and thousands of users?

here is table structure

CREATE TABLE friend_bulletin (
auto_id int(11) NOT NULL auto_increment,
user_id int(10) NOT NULL default '0',
bulletin text NOT NULL,
subject varchar(255) NOT NULL default '',
submit_date datetime NOT NULL default '0000-00-00 00:00:00',
status enum('Active','In Active') NOT NULL default 'Active',
PRIMARY KEY (auto_id),
KEY user_id (user_id)

Sxooter

This really depends very much on the database (or in the case of MySQL the table handler you're using)

For pgsql is doesn't matter much, except in terms of data to transfer, and since the subject is varchar(255) it's likely to be the majority of data transferred anyway.

btw, do you know what int(11) really means? I find that syntax used by a lot of MySQL users who think it means an int of no more than 11 digits...

jasondavis

Sxooter wrote:
This really depends very much on the database (or in the case of MySQL the table handler you're using)

For pgsql is doesn't matter much, except in terms of data to transfer, and since the subject is varchar(255) it's likely to be the majority of data transferred anyway.

btw, do you know what int(11) really means? I find that syntax used by a lot of MySQL users who think it means an int of no more than 11 digits...

is there something better then int(11)

Sxooter

jasondavis wrote:
is there something better then int(11)

That didn't answer my question. 🙂 I assume you wanted an int type of maximum size 11 digits. int(11) doesn't do that. It space pads the ints it returns to be 11 characters wide.

numeric(11) or decimal(11) will do what you want, but the performance won't be quite as fast as an int. Well, nothing's as fast as an int usually anyway. Nice thing about numeric though is you're not limited to +/- 2^31.

Roger_Ramjet

As Sxooter sez, there will be next to nothing between using * or a column list - despite what many say here there usually isn't unless you only want a few columns from a wide table.

The advantage that select * does have is that you can modify a table at a later date and not have to modify all the queries as well. That is a very useful thing when dealing with db apps that will have a long life and go through various generations of development - especially if you abstract and generalise your result processing procedures as well.