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

In this article, the editor introduces in detail "count (1) faster than count (*)" with detailed content, clear steps and proper handling of details. I hope this article "count (1) is faster than count (*)" can help you solve your doubts.

At some point, count (1) takes more time than count (*). You're talking nonsense. I don't understand, do you?

If I told you that MyISAM would store the total number of lines directly, InnoDB would not, and would need to scan by line. When the query condition is full table, that is, when there is no where condition, MyISAM will return instantly.

In other words: for select count (*) from xttblog;, if the amount of data is large, MyISAM will return instantly, while InnoDB will scan line by line. Especially for tables with a large amount of data, InnoDB should not easily select count (*), which consumes a lot of performance.

Only by querying the total number of rows in the whole table will MyISAM return the result directly. When the where condition is added, the two storage engines handle it in a similar way.

For example:

one

Xttblog (id, name, url, text, keyword)

Id is a competition

Keyword has index

Execute the following SQL statement:

one

Select count (*) xttblog where keyword = 'java' and name ='

At this time, for the above query statement, the two storage engines handle it in a similar way, and both need to perform index scanning.

But no matter what kind of storage engine, you have to build an index.

Count (1) is equivalent to count (*) in query results, but at some point, query time can refresh your values.

Let's talk about the differences among count (*), count (1) and count (listed):

Count (*) includes all columns, which are equivalent to the number of rows. When counting the results, the column value of NULL is not ignored.

Count (1) includes ignoring all columns, using 1 for lines of code, and not ignoring that the column value is NULL when counting the results.

Count (column name) only includes the column name. When counting the result, it ignores the count when the column value is empty (the empty value here is not just an empty string or 0, but represents null), that is, when a field value is NULL, it is not counted.

For InnoDB, when the column name is primary key, count (column name) is faster than count (1); when the column name is not primary key, count (1) is faster than count (column name); if the table has multiple columns and no primary key, then count (1) performs better than count (*); if there is a primary key, then the execution efficiency of select count (primary key) is the best; if the table has only one field, then select count (*) is the best.

After reading this, the article "is count (1) faster than count (*)" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself. If you want to know more about related articles, 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.