[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP SQL Syntax with MySQL question

Posted on 2008-11-17
9
Medium Priority
?
204 Views
Last Modified: 2013-12-13
I have a table with close to 100 columns that I am currently doing a select * on.

Is it better to only select the columns I need?

Instead of the SELECT * how do I name individual column in the the query?  SELECT columna, columnb, columnc ?
0
Comment
Question by:pda4me
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1600 total points
ID: 22979300
Instead of the SELECT * how do I name individual column in the the query?  SELECT columna, columnb, columnc ?

Yes, exactly.  Performance will improve, too!

Best, ~Ray
0
 
LVL 26

Expert Comment

by:tigin44
ID: 22979374
Sure you may provide the name of the columns in the select list

Select col1, col2, col3, .... coln
from TableName
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 22979441
It is definitely more efficient to select only those columns you are going to use.

If you doubt, you can set up a quick benchmark recording the time before and after queries, and see how much better.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 22979758
the explaination is mainly in the amount of data that has to be kept in the buffers on the server side, then to be transported over the network, then stored in the buffers on the client side, and finally having an array/structure for 100 items while the application only needs 3-4 of them, but has to get through all the items eventually.

it's like you would buy an entire library, just to get 3-4 books you want to read.
0
 

Author Closing Comment

by:pda4me
ID: 31517600
Thanks!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22979935
@angelIII: very well put! ~Ray
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22980009
>it's like you would buy an entire library, just to get 3-4 books you want to read.
additionally, developers doing this kind of code (select *) are then the ones complaining about network being slow and the like :/

glad to be of assistance ...
0
 

Author Comment

by:pda4me
ID: 22980270
Yep, I just changed the select * to just the few columns I needed and its an AMAZING difference...this sure beats the cost of the upgraded server the sales rep was trying to "upgrade" us to...ha!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 22980283
Bet you paid for your EE membership right there!

If you're looking for even more speed, you can try making those selection fields into indexes.

Warm regards, ~Ray
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question