Shulou(Shulou.com)06/01 Report--

This article mainly introduces the usage of count () in the mysql table, which has a certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

A single table contains 6000 W + data, but you can't split it. We need to count how much data there are in the table, how many products An and how many products B have.

Before optimizing for. The structure of the table is as follows. I blurred the corresponding fields in order to hide the contents.

CREATE TABLE `xxxx` (`link`varchar (200) DEFAULT NULL, `test0` varchar (500) DEFAULT NULL, `test1` varchar (50) DEFAULT NULL, `test2` int (11) DEFAULT NULL, `test3` varchar (20) DEFAULT NULL, `test4` varchar (50) DEFAULT NULL, `test5` varchar (50) NOT NULL, `inserttime` datetime DEFAULT NULL, `test6` bit (1) NOT NULL DEFAULT baggage, `A`bit (1) NOT NULL DEFAULT bread0mm, `B` bit (1) NOT NULL DEFAULT bread0mm, PRIMARY KEY (`test5`), KEY `test6` (`test6`) USING BTREE KEY `A` (`A`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8

This is a regular InnoDB table, so its count (*) is much slower than MyISAM, and the number of row rows displayed by InnoDB is not very accurate, so I need to count it here. There are several strategies.

Total 61500000 data

Count (*) takes 1539.499s

Count (1) takes 907.581s

Count (A) count the index.

Count (test6) count the primary key.

Without exception, because this table is not optimized, either of the above will take thousands of seconds, which we can't stand.

Let's begin to analyze and deal with this problem.

It is expected that the count (*) of the whole table should be normal within 200s, good within 100s and excellent within 50 seconds.

First of all, I extracted the test6 from it and formed a separate table. Operate on it.

Total 61500000 data

Count (*) takes 10.238s

Count (1) takes 8.710s

Count (test6) count the primary key. It takes 12.957s.

Among them, count (1) is the most efficient, which is 52.0% faster than the slowest count (competition).

Change the field you can determine to the optimal value, for example:

Varchar is more char. Although varchar can automatically allocate the size of storage space, .varchar needs to use 1 to 2 additional bytes to record the length of the string, increasing the operation time of its update.

Datetime was changed to timestamp, which was between 1978 and 2038.

Finally, the fastest time to use count (1) test is 168s. It's a little slow but acceptable.

Summary:

Redesign the fields in your table to optimize their length as much as possible. Don't blindly use too much varchar.

Use count (1) instead of count (*) to retrieve.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the usage cases of count () in the mysql table. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.