Delve into MySQL's varchar and limit (easily overlooked knowledge)

Why does the title have this name? Commen sence refers to things that everyone should know about, but often people will skip these things, or have a smattering of these things, today I summarize some of the commen sense types of problems I have encountered in MySQL.

1, varchar (5) can store how many Chinese characters, how many alphanumeric?

I believe there are many people who should be like me, this is already familiar, according to experience we can quickly make a decision, such as using varchar (200) to store the URL and so on, but even if you are familiar with many times, it is possible to make a wrong answer to the above questions.

This question I looked up a lot of information, some people say can store 5 characters, 2.5 Chinese characters (two bytes per Chinese character), some people say that to distinguish between the version, 5.0 is a dividing line, 5.0 before that, 5.0 can store 5 "word", do not distinguish between numbers, English, Chinese characters, If so, let's do an experiment:

The code is as follows:
CREATE TABLE ' Test ' (
' Name ' varchar (5) Not NULL DEFAULT ',
' Info ' char (5) Not NULL DEFAULT ',
PRIMARY KEY (' name ')
) Engine=myisam DEFAULT Charset=utf8;

It can be seen that the 5 in varchar (5) Represents 5 "words", instead of 5 bytes (bytes), when we store longer than the length of the set will be more than "click" Off, my MySQL version is 5.6, character set (charset) UTF8 and GBK is the same.

Other versions I have no computer, go to the official documents to see if there is any explanation, in the official document for a long time finally found a point of difference:

The following section, from Http://dev.mysql.com/doc/refman/4.1/en/char.html, is a description of mysq4.1:

The code is as follows:
The CHAR and VARCHAR types are declared with a length this indicates the maximum number of characters to store. For example, CHAR (a) can hold up to characters. (Before MySQL 4.1, the length is interpreted as number of bytes.)

Let's look at a similar note in other versions:

The code is as follows:
The CHAR and VARCHAR types are declared with a length this indicates the maximum number of characters to store. For example, CHAR (a) can hold up to characters.

Obviously, the official document says the MySQL version is less than 4.1 when stored in accordance with the saying: varchar (5) Save 5 bytes, and 5 English digits or 2.5 Chinese characters (assuming a Chinese character 2 bytes);

MySQL version is greater than or equal to 4.1 when varchar (5) of 5 is no longer the number of bytes, it should be understood as "word" the word here means a Chinese character and an English or digital "same treatment"

2, MySQL in the limit, you really can use it?

How do you use limit in your project? Limit num or limit num1,num2? Or is it something else? Be aware that limit uses different forms of performance gaps.

I tested it myself, in a InnoDB table to use limit, table 10,000 data, four fields, id (int), time (int), title (varchar), the body (mediumtext), size about 170M, First turn off the query cache, so that the query cache has no effect on the query time, notice here to add an index to the Times field,

The code is as follows:
SET @ @query_cache_type =on;
SET GLOBAL query_cache_size=0;

Open Query Profiler to see the time it takes to execute the statement

The code is as follows:
Set profiling=1;

Next, execute on the following statements

The code is as follows:
A, SELECT id,time,title from Cnblogs WHERE time>=1315646940 order by time ASC LIMIT 2000,10

B, SELECT id,time,title from Cnblogs WHERE time>=1315646940 order by time ASC LIMIT 10

C, SELECT Id,time,title from Cnblogs order by time ASC LIMIT 3000,10

Execution Order A,b,c,a,b,c,c,a,a (note here, although I turned off the cache, but the last query was cached, this can be seen from query Profiler, so cross execution), use the following statement to view the results

The code is as follows:
Show Profiles;

From the above statement execution time analysis can be seen, regardless of caching factors, when the use of limit, "limit begin,num" this form than "limit num" This form of efficiency is much lower, so use the time to use the second form as much as possible, For example, to loop through the data in a table, once removed from the inside out, this time will be according to the ID (or other sort of fields) to limit, we can get the last of the field threshold as the next time to take the minimum value of the data, using limit num This form of high efficiency.