mysql> select count(*) from num;
+----------+
| count(*) |
+----------+
| 9918530 |
+----------+
1 row in set (59.18 sec) isn"t that scary?
I switched to sqlite3 and got it in 2 seconds.
mysql> select count(*) from num;
+----------+
| count(*) |
+----------+
| 9918530 |
+----------+
1 row in set (59.18 sec) isn"t that scary?
I switched to sqlite3 and got it in 2 seconds.
Don't listen to some of the misleading above, let you count the primary key, the effect is the same. Because mysql found that the inside of your count primary key will directly replace it with count (*).
because I don't know your table structure and index status, I can only guess and give advice:
1. I guess you are not using the myisam engine, because the count () under the mysaim engine is recorded in the schema table and you can get the numbers directly. If your count () is conditional, then myisam will also slow down.
2. It seems as if you should be using the innodb engine, you use count (*) is the right choice, but it is slow. For better optimization, please refer to adding redis or memcache cache. If you do not want to introduce a new storage structure, you can record the total by creating a redundant field in mysql.
you can read the relevant chapter of "High performance mysql".
MySQL High performance third Edition
Chapter 6, page 236
there are already tens of millions of pieces of data. This should not be counted in this way.
MyISAM engine will have automatic maintenance of this, it can be faster.
10 rows in set (0.49 sec)
< table > < thead > < tr > < th > mysql > select count (id) from num; < / th > < / tr > < / thead > < tbody > < tr > < td > count (id) < / td > < / tr > < tr > < td > 9918530 < / td > < / tr > < / tbody > < / table >1 row in set (46.95 sec)
< table > < thead > < tr > < th > mysql > select count (adsh) from num; < / th > < / tr > < / thead > < tbody > < tr > < td > count (adsh) < / td > < / tr > < tr > < td > 9918530 < / td > < / tr > < / tbody > < / table >1 row in set (30.91 sec)
< table > < thead > < tr > < th > mysql > select count (tag) from num; < / th > < / tr > < / thead > < tbody > < tr > < td > count (tag) < / td > < / tr > < tr > < td > 9918530 < / td > < / tr > < / tbody > < / table >1 row in set (27.54 sec)
mysql > ALTER TABLE num ENGINE = 'MYISAM';
Query OK, 9918530 rows affected (3 min 5.07 sec)
Records: 9918530 Duplicates: 0 Warnings: 0
mysql> select count(*) from num;
+----------+
| count(*) |
+----------+
| 9918530 |
+----------+
1 row in set (0.02 sec)Why is the variable length type query faster than the primary key?
mysql turns on query caching, which is definitely less than 2 seconds. If writes are rare, turn on the query cache
memory library is faster than the database to read IO. Is there a problem? It is strange that sqlite is slower than mysql
Previous: How to call internal functions externally?
Next: The usage and difference between vue mixins and extends
$selectTip = $pdo->query("SELECT * FROM `tips_rate` WHERE `tip_id` = ".$row[ id ]." " ); $selecttotal = mysqli_num_rows($selectTip); $rate = $pdo->query("SELEC...
for example, the isolation level of transaction An is repeatable read my understanding is that a read sql statement of transaction An is the same as the result repeated many times, regardless of whether or not other transactions have changed the data ...
I know that mysql master and slave have been configured, but I don t know what to do if one of them dies: 1. If there is only one master and one slave, do you want to upgrade the master manually in case the master dies? (it seems that you can t call...
subject table, which can be understood as a classification table. The course and source_book tables are associated with subject now use the subject table as the main table to query how many courses and books are under this category. query statemen...
do a check-in function `create_time` decimal(20,3) NOT NULL, `create_date_ymd` varchar(15) NOT NULL COMMENT , `create_date_ym` varchar(15) NOT NULL, `create_date_y` int(4) NOT NULL, `create_date_m` int(2) NOT NULL, `create_date_d` int(2) ...
as shown in the following figure the current time is 16 41 00. I want to take this section of data every two hours. How should sql write ? ...
1. Problem description assume that there are n game order tables in the database, and the table name is game_ [0-9a-z] + _ order. . It is known that there is an order number 18demo000001. How can we efficiently find out which game table this order is i...
I want to optimize a sentence about group by. I don t know where to start. I want to find a train of thought . SELECT adv_id, network_id, SUM(re_click) AS clk, SUM(deny_click) AS dny_clk, SUM(re_conver) AS re_ins, SUM(mat_conver) AS mat_ins, SUM(def_...
background: 1. I am a java programmer 2. Recently interviewed for a senior development position 3. The previous project mainly used less oracle,mysql, and the companies interviewed basically used mysql s 4. I am weak in the database 5. Recently, I...
like this SELECT * FROM material WHERE {key:value} limit 1 ...
how does sql write from the database table to the display of the page? requirement: the first-level classification needs sorting, the first-level classification shows its subcategories, and the subcategories also need sorting. ...
check-in table: fields include user id userid check-in time signtime timestamp type int check-in type type is divided into 0 and 1 for check-in and check-out, respectively. A user can sign twice a day, once check-in and one check-out. Check-in and ch...
Table structure ...
use mybatis to configure custom secondary cache. Using xml annotation can use < cache type= "com.test.dao.MybatisRedisCache " > to successfully use custom cache, but using @ CacheNamespace (implementation = MybatisRedisCache.class) on the dao i...
class essay extends Model { public function comment() { return $this->hasMany( App comment ) } } class comment extends Model { public function essay() { return $this->belongsTo( App essay ) } } ...
mybatis+mysql for java projects int batchAmount = 20000; for (int startIndex = 0; startIndex < size; startIndex += batchAmount) { int endIndex = startIndex + batchAmount; endIndex = (endIndex &...
Master and Slave built by mysql5.6, the newly created table is not backed up to the slave library master and slvae data can both correspond to can t the test library be automatically backed up to the slave library when establishing a master-slav...
I want to do a circular sequence to output pictures. Don t know how to write this code? Table structure for example, now the field ID of key=1 is 1, 2, 5, 7, 10, 11, 6 fields I want to output one at a time in order. output id 1 field for t...
for example, if the database has a field type of enum ( axiomagementific database ), can you change this field to enum when there are millions of data in the database? will it be slow? ...
ask God to help me translate this native SQL statement into TP5 thank you! It is Db:: ( name )-> this kind of chained operation select a.id, a.titlerect a. Createwriting time as answer_count from fa_question a. Isometric solvepaper count (b.qid) as ...