Search Postgresql Archives

Re: (Select *) vs. (Select id) from table.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Saturday 07 January 2006 13:50, Michael Trausch wrote:
> Mike wrote:
> > Hi,
> >
> > I am trying to make a website where scalability matters. In my quest to
> > make my website more scalable I broke down the following SQL statement:
> >
> > select * from customers limit 100
> >
> > to:
> >
> > select id, updated_date from customers limit 100
> >
> > Then my application would check it's cache to see if it has those
> > records available and will hit the database with consequent:
> >
> > select  * from customers where id = 4 or id = 9 or id = 19
> >
> > Am I really speeding things up by breaking down the SQL statements to
> > what's necessary? or is it faster to get everything right at once!
>
> Well, first, it's never really a good idea to use "SELECT * FROM" in a
> production application, against a table.  Tables can (and do) change
> from one release to another, and if the layout of the table changes, you
> could be looking at having to rewrite your code, especially if it relied
> on the order of the columns in the tables.  It's always better to
> specify the columns that you're looking for, since existing columns
> should (at least in theory on a production DB) remain present, though
> their order can change sometimes, depending on what the DBA does.  :)
>
> Secondly, as far as making your queries more efficient, the only way
> that you can really do that is to determine actually how long the
> queries are taking.  This is relative to the size of the database in
> rows, and of course, the data that you're querying against, whether a
> table scan is necessary, and all of that.  This is the process of
> optimizing queries.  For a small table, it can be faster sometimes to
> just pull all of the records at once (for example, if they're all within
> a single page).  However, if you're pulling from a large DB, it will be
> faster to use smaller queries against it, using well-placed indexes.
>
> Also, you may wish to consider using views if you really like using *
> with SELECT... Since this way, you can just depreciate a view and start
> using a new one if the underlying columns are changed.  :)  Also, if you
> use views, you can optimize the view's query when it comes time to
> change it, which mess less messing around in the application code,
> especially if it is a frequently used query.
>
> 	HTH,
> 	Mike
>


If your application uses a kind of mapping algorithm that is based on the 
information schema you can use select * without a risk. I'd say a select * is 
slightly slower than a column name based select, simply because you transfer 
more information to the application. The time the DB needs to find the record 
should be the same since the record has to be found before the resultset is 
assembled.
I.e. my application uses an object-relational mapper and returns an object to 
the application. The object is assembled using the information schema and the 
columns asked for - if any are asked for specifically. Therefor my app does a 
lot of select *'s, but it won't break if I add, change or delete columns. It 
doesn't even break if I rename a column.
If you don't use something like that I'd go with the "better" approach of 
selecting by column name. The only time when this is considerably slower is 
while you program the queries (more typing :-) ).
Views are the way to go when you know there's going to be changes. Views allow 
you to define the "API" your application relies on. Rules allow you to make 
the view writeable, so you can effectively hide the layout of your database 
which is allways a good idea.

	UC

--
Open Source Solutions 4U, LLC	1618 Kelly St
Phone:  +1 707 568 3056		Santa Rosa, CA 95401
Cell:   +1 650 302 2405		United States
Fax:    +1 707 568 6416


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux