I recently read a paragraph that said doing% s_co% was very inefficient unlike when we pointed out the fields we want to use, but I need more information about that.
Could someone tell me where to find it or any experience I have had in this regard?
I recently read a paragraph that said doing% s_co% was very inefficient unlike when we pointed out the fields we want to use, but I need more information about that.
Could someone tell me where to find it or any experience I have had in this regard?
When used * is equivalent to using the ALL clause that selects all columns. This means that the defined indexes are not used. Vera, the indices are defined on one or several columns (few) of a table or view, enabling SQL to retrieve the rows and associated to the values defined in the key of that index more quickly and efficiently.
In addition, the mere fact of loading a large amount of data without restriction means that the database engine and other intervening elements must make a greater effort to handle that large amount of data that may not be necessary.
In SQl Server it is discouraged to use * because the database engine goes to the master table and queries the names of the columns before making the query and then executes it. This involves a double process. Probably a similar case in Mysql
This address contains information that may be useful: link
Making a query only with the fields you need is more efficient than consulting all the fields.
When you consult only the fields you need the database will be responsible for obtaining and returning only the information you requested. The more information you have to return the database, the longer it will take to respond.
I think there is a confusion with the use or not of the indexes. I will try to give my opinion on the question that was about the convenience or not of using SELECT * FROM ..
It is never recommended to use SELECT * FROM for various reasons
1.- Although it is a convenient practice, it may cause an application to work incorrectly in the future. Imagine a table with the fields A, C F. If you do a SELECT * then the answer will bring 3 fields; but if you add field B after A (leaving fields A, B, C, F), the same SELECT will "travel" fields C and F one place. If your program uses the fields by position (displays field 2) then everything will fail.
2.- The reading may be slower. Imagine a table with 30 fields (it's not uncommon) and you only really need 3 fields. What do you think is taking longer and occupying more space? ... Exactly, the SELECT * will have to read 30 fields of the disk and transfer them to the program instead to read only 3 and transfer 3.
3.- In the case of indexes it can be even beneficial if all the fields you want to extract are in the index key: If you do a SELECT of 2 fields, and those two fields are in an index, then only reading the index can be answered all the SELECT; on the other hand, if you do a SELECT * then the database engine will have to - perhaps - use the index and then make the "jump" to the disk to extract the rest of the data (in SQL Server they are called lookups when you see the execution plan).
4.- SQL Server, specifically, can make use of CLUSTER indexes that even help you much more, but it would be the subject of another question. Maybe MYSQL has something similar.
I hope I have contributed.
It would only be inefficient if you do not need all the tuples.
Select name, last name FROM table; when we want to extract only a selection of specific fields and it is of smaller quantity, to add them to a table or a form etc; but if what we want is to fill in a form of 30 fields it is not very logical to make the query by selecting the fields in question. An example to understand: we have the data table and we have 200 fields: name, age, telephone, address, cp, email, city, country, etc ... What are we going to select one by one the 200 fields, for it better to put them all with *?
I hope my information will help you.