performance in a select query

Asked

Viewed 102 times

-1

For DBMS what is faster to receive all the fields specified or use the character * which represents all fields. If there is a difference, why?

Select coluna1,coluna2,coluna3, .....,colunaN from

Or

Select * from

The question is quite clear, explicitar TODAS as colunas ou usar * and yet I would like an objective response focused only on that condition.

It is not desirable that the answers to this question be based on opinions but rather on facts, references or specific experience.

mysql sql

  • I believe that the fewer fields you request, the faster the query becomes. But let’s wait for the more experienced to speak.

  • 4

    Possible duplicate of Number of columns influences performance? and Why using "SELECT * FROM table" is bad?

  • 1

    The question was quite clear, "specify all columns or use *" and do not use whatever is necessary

  • 1

    For the DBMS it is faster to receive the command with all fields specified. Using * forces the server to query which fields are before searching the data, creating one more step in the process. Source http://www.devmedia.com.br/sql-select-guia-para-beginners/29530

  • When I started using Mysql I was instructed to select the fields I will use, even more when there are many in the table, or when I will JOIN.

  • @Leocaracciolo This is not totally true (ie it is true but not for all cases), each engine has a behavior, it may be that a query with * is optimized for a specific type of mysql engine, it is not possible to say anything, besides that in any way this is usually micro-optimization and therefore it is hardly this kind of thing that will harm the database, of course it depends a lot on the server, engine and/or settings.

  • @Please read the answers to this question, see if it answers: https://answall.com/q/21583/3635

Show 2 more comments

1 answer

0


There are some researches that suggest that making the field explicit in a query makes the query faster, since the use of the * the database must search for existing tables and only then display their contents. It depends a lot on the amount of data you have, but it is best to specify which columns are. If you use Mysql with PHP, and there is a change in table structure like adding a new column, it may result in significant damage to your system if the query is made with the *, or it can even cause unimportant data to flow over the network using unnecessary bandwidth and processing.

http://weblogs.asp.net/jgalloway/archive/2007/07/18/the-real-reason-select-queries-are-bad-index-coverage.aspx

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.