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

This article mainly gives you a brief introduction to the test environment of millions of data mysql. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the test environment of millions of data mysql to talk about the topic, hoping to bring you some practical help.

Test environment

The total number of 3 million pieces of data takes up about 1G of disk space.

Data structure

Table 1 news [article table engine myisam character set utf-8]-idint11 primary key automatically adds cateint11 index titlevarchar200 title (for basic search) contenttext article body dateint11 article release time (in the form of timestamp) Table 2 cate [ Article classification table engine myisam character set utf-8]-cate_idint11 primary key automatically adds cate_namevarchar200 article title

Total number of queries

Select count (*) as total from news// under myIsam engine takes 0.001 seconds / / with conditional select count (*) as total from news where cate = 1 takes 0.046 seconds acceptable speed select count (*) as total from news// under innodb engine takes 0.7 seconds very slow select count (*) as total from news where cate = 1 takes 0.7 seconds very slow

Why is there such a big difference in query speed between the two engines?

The specific number of rows of the table is not saved in InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are.

MyISAM can simply read out the saved rows.

Note that when the count (*) statement contains the where condition, the operations of the two tables are somewhat different, with the InnoDB type table using count (*) or count (primary key), plus the where col condition. Where the col column is a column with a unique constraint index other than the primary key of the table. In this way, the query will be very fast. It is possible to avoid full table scanning.

Summary

Mysql uses count (*) to query the total number of data in the case of 3 million pieces of data (myisam engine). The query contains the condition (the index is set correctly) and the running time is normal. We recommend using the myIsam engine for data that is read frequently.

This is the end of the test environment for millions of data mysql. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.