MYSQL: SQL _CALC_FOUND_ROWS and count (*) performance comparison _ MySQL

MYSQL: SQL _CALC_FOUND_ROWS and count (*) performance comparison bitsCN.com

1. create a table:

// Overwrite the index create table if not exists 'Ben' ('A' int (10) unsigned not null AUTO_INCREMENT, 'BB' int (10) unsigned not null, 'CC' varchar (100) not null, primary key ('A'), KEY 'bar' ('BB ', 'A') ENGINE = MyISAM; // no overwriting index drop table if exists 'Ben'; create table if not exists 'Ben' ('A' int (10) unsigned not null AUTO_INCREMENT, 'BB' int (10) unsigned not null, 'CC' varchar (100) not null, primary key ('AA'), KEY 'bar' ('BB ')) ENGINE = MyISAM;

2. Insert 1 million data records:

// Insert data function insertData () {// Insert 1 million data records into the data table $ ben = new Model (); for ($ I = 0; $ I <1000000; $ I ++) {$ a = $ I % 2 = 0? 1: 0; $ SQL = "insert into ben values (null ,". $. ",'". md5 ($ I ). "')"; $ aa = $ ben-> query ($ SQL );}}

3. read data and test program execution time:

Function read01 () {$ start = microtime (true); $ ben = new Model (); $ ben-> query ("select count (*) from ben where bb = 1 "); $ ben-> query (" select aa from ben where bb = 1 limit 100,10 "); $ end = microtime (true ); echo $ end-$ start; // covering index: 0.018204927444458, no covering index: 0.017701148986816} function read02 () {$ start = microtime (true ); $ ben = new Model (); $ ben-> query ("select SQL _CALC_FOUND_ROWS aa from ben where bb = 1 limit 100,10"); $ ben-> query ("select FOUND_ROWS () "); $ end = microtime (true); echo $ end-$ start; // covering index: 0.017460823059082, no covering index: 0.20762395858765}

4. conclusion:

When an index is overwritten, SQL _CALC_FOUND_ROWS provides high performance. If no index is overwritten, the performance of count (*) is high. In addition, the innoDB engine is lower than MyISAM.

Appendix:

Document 1: covering indexes

Select aa from ben where bb = 1;

1) Total number of statistics:
Select count (*) from ben where bb = 1;
After we create an Index in bb, this query uses Covering Index ).

2) in the paging operation:
Select id, title, content from ben order by createtTime desc limit, 10;

In general, the index will be created in the createdTime field (where id is the primary key). However, when the limit offset is large, the query efficiency is still very low. overwrite the index to improve the performance:
Create index indexName ON ben ('createtime', 'id'). you can use explain to check whether the index is overwritten. if extra: using INDEX is

Document 2: differences between engine MyISAM and InnoDB

InnoDB and MyISAM are the two most common table types used by many people when using MySQL. the Two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does.

MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

The following are some differences between details and specific implementations:

◆ 1. InnoDB does not support FULLTEXT indexes.

◆ 2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note:

Yes. when the count (*) statement contains the where condition, the operations on the two tables are the same.

◆ 3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

◆ 4. when deleting FROM table, InnoDB does not create a new table, but deletes a row.

◆ 5. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, tables that use additional InnoDB features (such as foreign keys) are not

Applicable.

In addition, the row lock of the InnoDB table is not absolute. if MySQL cannot determine the scope to be scanned when an SQL statement is executed, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

The main difference between the two types is that Innodb supports transaction processing and foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.

From the perspective of MySQL Users, Innodb and MyISAM both prefer to use MySQL, but from the perspective of my current O & M database platform, we need to meet the following requirements: 99.9% stability, for convenient scalability and high availability, MyISAM is definitely me

First choice.

The reason is as follows:

1. first, most of the projects on my current platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.

2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. If compression is not used, the Innodb ratio is

MyISAM is huge.

3. from the platform perspective, it is often the next two months that application developers may accidentally update the where write range of a table, which makes the table unusable, at this time, the superiority of MyISAM is shown.

Take out the file of the corresponding table from the zip file of Bay, place it in a database Directory, dump it into SQL, export it back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. don't tell me to let Innodb regularly use export

Xxx. SQL mechanism backup, because the data volume of the smallest database instance on my platform is basically dozens of GB.

4. from the application logic I have come into contact with, select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, Innodb locks tables. many people think Innodb is row-level.

The lock, that is, the where clause is valid for its primary key. all non-primary keys lock the entire table.

5. there are often many application departments that need to regularly provide some table data to them. for MyISAM, it is very convenient to send it to the corresponding table's frm. MYD, MYI files, so that they can be started in the corresponding version of the database, and

Innodb needs to export xxx. SQL, because only files are provided to others, and the other party cannot use the dictionary data files.

6. if Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the write performance of MyISAM. if it is an index-based update operation, although MyISAM may be inferior to Innodb, however, whether the slave database can catch up with highly concurrent writes

It is better to solve this problem through the multi-instance database/table sharding architecture.

7. if MyISAM is used, the merge engine can greatly accelerate the development speed of the application department. they only need to perform some select count (*) operations on the merge table, it is very suitable for a type of rows with a total number of hundreds of millions of projects (such as logs and surveys ).

Business table.

Of course, Innodb is not absolutely unnecessary. I use Innodb to simulate stock trading projects with transaction projects, for example. when more than 0.2 million of active users are active, it is also very easy to cope, therefore, I personally like Innodb, but if the database platform should

Start with MyISAM.

In addition, some people may say that MyISAM cannot defend against too many write operations, but I can make up for it through the architecture, saying that my existing database platform capacity is: there are several major items on the dynamic pages with a total master/slave data volume of more than several hundred TB and a daily pv of more than million

The total number of PVS is not calculated by calling data interfaces. (this includes a single database that processes tens of millions of queries per day because memcached is not deployed in the initial stage ). The average load of my entire database server is between 0.5 and 1.

BitsCN.com