Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the differences between all the fields of select * and select. What is introduced in this article is very detailed and has a certain reference value. Friends who are interested must read it!

MySQL 5.1.37

The number of records in the table is 41, 547, 002, that is, 4000 rows.

Use the remote client to fetch 1000 pieces of data and count the time:

SELECT * FROM dmsp.dmsp_dimension_content LIMIT 0, 1000

The time is 2.218 s and the network consumption is 0.547 s.

SELECT id, appid, aop, t, uid, sid, pid, pname, bid, bname, ptype, sm, sv, bt, national, area, ov FROM dmsp.dmsp_dimension_content LIMIT 0, 1000

Take out all fields, time 2.250s, network consumption 0.578s.

Multiple queries (changing limit conditions to avoid caching), the time does not change much.

Conclusion: the difference between them is almost negligible. So when querying all fields (or most of them), you can select *. If some unwanted fields have a particularly large amount of data, it is better to write the fields clearly, because this can reduce network transmission.

(1) reduce the burden of data.

SELECT * requires the database to Query Table Metadata For Columns first, which increases the burden on the database to some extent (affecting the performance of network transmission), but in fact, there is little difference in efficiency between the two.

(2) considering the expansibility in the future.

Because the columns you need to use in the program are certain after all, SELECT * just reduces the length of a SQL String sentence, not the code elsewhere.

(3) Index problem

Select abc from table; and select * from table

If the abc field has an index, mysql can return the result without reading the data and directly using the value in the index. But once select * is used, there will be other columns to read, and then you need to read data after reading the index to return the result, which results in additional performance overhead.

To sum up: except for daily practice, SELECT * FROM XXX is not recommended in other situations.

The above is all the content of the article "what are the differences between all fields of select * and select". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.