SQL performance ( MySQL )

Question

i have this tabel,

CREATE TABLE `forum_rank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL DEFAULT '0',
  `rank` int(11) NOT NULL DEFAULT '0',
  `forum_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

now i ask about what perfome best, its * or alle felt like this 2 eg.

select * form forum_rank;

or

select id, user_id, rank, forum_id from forum_rank;

Florin Dumitrescu · Accepted Answer · 2010-11-16 10:48:00Z

4

You should explicitly specify the columns. Otherwise the database engine will first have to find out what the table's columns are (resolve the * operator) and after perform the actual query.

answered Nov 16, 2010 at 10:48

Florin Dumitrescu

8,3124 gold badges35 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

duffymo · Accepted Answer · 2010-11-16 10:46:05Z

4

I don't think performance will be a problem here. There's a better reason to prefer the second idiom: your code is less likely to break if you add additional columns.

answered Nov 16, 2010 at 10:46

duffymo

310k46 gold badges376 silver badges571 bronze badges

6 Comments

Florin Dumitrescu Over a year ago

Hi, I might be missing something in here, but why queries using the * operator would break and those who don't wouldn't?

duffymo Over a year ago

If you're writing Java, and you get values out of the ResultSet that depend on column index, a change in indexes will result in the wrong item being mapped into your object.

Florin Dumitrescu Over a year ago

I am not sure I agree with you. If you add new columns to a table and use "select *", the new columns would be the last in the ResultSet so the first indexes should remain the same. Alternatively, if you select by column names and a add a new column in the select at any position but the last, this will indeed break the code. Retrieving data from ResultSets by index is considered a bad practice. As far as I know data should be retrieved by column names.

duffymo Over a year ago

I agree with your point about retrieving by column names, but it's not universally done. It's certainly possible to use column indexes. I'm not aware that the new columns are guaranteed to be last, but I could be wrong.

Danosaure Over a year ago

@Florin Dumistrescu: How does select by column names and adding a new column break the code? If you select by name, you only keep what you want/need. If that new column is not needed, the code is not broken. If that column is needed, you better know the select you are doing. Perhaps this is Java specific?

|

Collectives™ on Stack Overflow

SQL performance ( MySQL )

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related