About database Optimization The difference between count (1), COUNT (*), and count (column name), and questions about the order of fields in the table

1. The difference between count (1), COUNT (*), and count (column name)

Believe that you are always at work, or in the study of Count () in the end how to use faster. There has been a lot of doubt, some people say that count (*) faster, some people say count (column name) faster, that is who is faster, I will detail in this article is count (1), COUNT (*) and Count (listed) the difference, and more suitable for the use of the scene.

As usual at work someone would say that count (1) would be faster than count (*), or instead, first this conclusion must be wrong, actually count (1) and COUNT (*) do not differ .

Next, let's compare the count (*) and Count (column) to who is faster

First we execute the following SQL to take a look at the efficiency of execution (the following SQL is for the Oracle database, the approximate logic is to first delete T, and then create a T table based on Dba_objects, in the update T table according to RowNum)

1 Drop TableT purge;2 Create TableT as Select *  fromdba_objects;3 --ALTER TABLE T modify OBJECT_ID null;4 UpdateTSet object_id =rownum;5 SetTiming on 6 SetLinesize +7 SetAutotrace on--Turn on tracking8 9 Select Count(*) fromT;Ten / One Select Count(object_id) fromT; A /

Then let's take a look at "Select count (*) from T" and "Select count (object_id) from T The Execution plan for the statement. (An execution plan is a sequence of execution and resources consumed by SQL that consumes less resources faster, and if you use F8 in Plsql, you can view the SQL execution plan)

　　

　　

　　In our experiments with SQL, COUNT (*) and count (columns) consume the same resources, saying they are as fast as they are, but really. So let's proceed to the following experiment.

　　Let's give object_id This column an index and try it. Let's execute the index SQL

 1  create  index  idx_object_id on  t (object_id  );  2  select   Count  (* ) from   T;  3  / 4  5  6   Select  count  (object_id ) from   T;  7  / 

And then we're looking at two SQL execution plans separately.

　　

　　

　　After we have finished building the index. It suddenly turns out that count (column) is getting a lot faster, but count (*) is the same as before. This shows that the count (column) can be used for the index, and COUNT (*) does not work, but really, we are looking down.

　　Next we add a non-nullable condition to the object_id field. We execute the following SQL

 1  create  index  idx_object_id on  t (object_id  );  2  select   Count  (* ) from   T;  3  / 4  5  6   Select  count  (object_id ) from   T;  7  / 

Next we're going to look at the execution plan for count (*)

　　

　　Now Count (*) is as fast as Count (column), so we come to the conclusion that count (column) and COUNT (*) are actually as fast as if the index column is non-empty, and COUNT (*) is available to the index at this time as fast.

Summary: But the real conclusion is this. actually otherwise In fact, in the database count (*) and Count (column) is not equivalent,count (*) is for the whole table , and Count (column) is for a column , if the value of this column is empty, Count (column) is not going to count this line. So there is no comparability at all, performance comparison first of all to consider the equivalence of the wording, these two statements are not equivalent. Also lost to compare the meaning!!!

2. Questions about the order of fields in a table

　　First we build a table with 25 fields and add data to the count (*) and Count (column) comparisons. Because the build statement and INSERT statement are identical to the above. Don't put out the code.

Then we do the count (*) and count each column to see who is faster, because there are too many execution plans, it is not mapped. I've compiled an Excel to show you the results of the execution.

　　

After the experiment we see that the more The Count (column) is. The slower we execute. So, we come to the following conclusion:

1. The offset of the column determines the performance, the more the column, the greater the cost of access .

2. Because the Count (*) algorithm is independent of the column offset, COUNT (*) is the fastest.

　　Summary: So we are in the development of design. The more commonly used columns, place them in the front position. While Cout (*) and Count (column) are two unequal-price usages, it is not possible to compare which performance is better, and to consider whether to use COUNT (*) or Count (column) in the actual SQL optimization scenario based on the prevailing business scenario (where the difference is mentioned above).

　　

　　

　　

　　

　　

　　

About database Optimization The difference between count (1), COUNT (*), and count (column name), and questions about the order of fields in the table