MySQL a few details minor issues

If, in MySQL to build a table 1, named A, only one column called "ID", there are three values, respectively "1" "2" and null, Note that this is null, not "null", if it is "null", then the input is actually 0.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/79/9E/wKioL1aWcpOA4T6iAADDEAWNmjs806.png "title=" Mysql-1.png "alt=" Wkiol1awcpoa4t6iaaddeawnmjs806.png "/>

The element in table A is 1 2 3 null, but the result of Count (*) and COUNT (ID) is not the same value, thus thecount (ID) is more accurate because he does not include NULL.

We continue, if we build a table B, there is a column called b_id, there are two elements, all 1, and now intends to "show a table in the same as the B-table ID number", there are two methods of expression, one is a subquery, a join query, generally using a join query, the JOIN statement is better written, Effects such as.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/79/9E/wKioL1aWc2jztMLnAADClFcZ-aA943.png "title=" Mysql-3.png "alt=" Wkiol1awc2jztmlnaadclfcz-aa943.png "/> used the subquery for the first time, returned a" 1 ", the second time with the join statement, returned two" 1 ", In other words, there is a one-to-many situation where a distinct is added before the join to cancel the duplicates .

Assuming there is a larger table in the server, each select will be commuting, so in explain select is a good way, the table in the example structure such as:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/79/9F/wKioL1aWeKOTHU3gAABqHWXaWaU430.png "title=" Mysql-1.png "alt=" Wkiol1awekothu3gaabqhwxawau430.png "/> and two explain results are very different:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/79/9F/wKioL1aWeL3gFyvlAABQyJswKGU488.png "title=" Mysql-2.png "alt=" Wkiol1awel3gfyvlaabqyjswkgu488.png "/>

LIMIT 50,5 is starting from 51, to 55 end, not including 50!

one scanned only 55 lines, one scanned 953 lines, and the extra steps were used, thus, to optimize the limit query, the main thrust is to scan the data as little as possible, so you can use the primary key to use the primary key. My example here is only starting from 50 lines, if it is in production, table tens of thousands of rows and even hundreds of thousands of rows of data, the primary key scanning advantage is very obvious.

Not yet, if we are not afraid of tired, a few more words.

SELECT film_id,description,title from Sakila.film WHERE film_id >55 and film_id <=60 ORDER by title LIMIT 0,5 \g

Look at the explain of this command, the scan number is more gratifying ~LIMIT 0,5 is not 1, 5, if 1, the beginning is the 57th. Be careful here!

Focus on the back, this usage is mainly used for Yimapingchuan primary key data table , the middle primary key can not be used in the empty block, meaning that the table can not have a drop record, otherwise, will appear real empty file.

This article is from "Life is waiting for Gordo" blog, please make sure to keep this source http://chenx1242.blog.51cto.com/10430133/1734817

MySQL a few details minor issues