Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to improve the efficiency of MySQL query". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Because of its small size and efficient operation, MySQL is more and more used in database applications. I used to use MySQL to save P2P nodes when developing a P2P application. Because there are tens of thousands of nodes in P2P applications, and the nodes change frequently, we must keep the query and insertion efficient. Here are three effective attempts to improve efficiency in the course of use.

L use statement for binding query

Using statement, you can build a query syntax tree in advance and query directly without building a syntax tree. Therefore, it can improve the efficiency of the query. This method is suitable for situations where the query condition is fixed but the query is very frequent.

The method of use is:

1. Bind, create a MYSQL_STMT variable, bind to the corresponding query string, the question mark in the string represents the variable to be passed in, each question mark must specify a variable.

two。 Query, enter each specified variable, pass in the MYSQL_STMT variable to execute with the available connection handle.

The code is as follows:

/ / 1. Binding

Bool CDBManager::BindInsertStmt (MYSQL * connecthandle)

{

/ / bind for insert operation

MYSQL_BIND insertbind[FEILD _ NUM]

If (m_stInsertParam = = NULL)

M_stInsertParam = new CHostCacheTable

M_stInsertStmt = mysql_stmt_init (connecthandle)

/ / build binding string

Char insertSQL[SQL _ LENGTH]

Strcpy (insertSQL, "insert into HostCache (SessionID, ChannelID, ISPType,"

"ExternalIP, ExternalPort, InternalIP, InternalPort)"

"values")

Mysql_stmt_prepare (m_stInsertStmt, insertSQL, strlen (insertSQL))

Int param_count= mysql_stmt_param_count (m_stInsertStmt)

If (param_count! = FEILD_NUM)

Return false

/ / populate the bind structure array. M_sInsertParam is the structure variable associated with this statement.

Memset (insertbind, 0, sizeof (insertbind))

Insertbind [0] .buffer _ type = MYSQL_TYPE_STRING

Insertbind [0] .buffer _ length = ID_LENGTH / *-1 * /

Insertbind [0] .buffer = (char *) mdetecstInsertParam-> sessionid

Insertbind [0] .is _ null = 0

Insertbind [0] .length = 0

Insertbind [1] .buffer _ type = MYSQL_TYPE_STRING

Insertbind [1] .buffer _ length = ID_LENGTH / *-1 * /

Insertbind [1] .buffer = (char *) mdetecstInsertParam-> channelid

Insertbind [1] .is _ null = 0

Insertbind [1] .length = 0

Insertbind [2] .buffer _ type = MYSQL_TYPE_TINY

Insertbind [2] .buffer = (char *) & mresolstInsertParam-> ISPtype

Insertbind [2] .is _ null = 0

Insertbind [2] .length = 0

Insertbind [3] .buffer _ type = MYSQL_TYPE_LONG

Insertbind [3] .buffer = (char *) & mresolstInsertParam-> externalIP

Insertbind [3] .is _ null = 0

Insertbind [3] .length = 0

Insertbind [4] .buffer _ type = MYSQL_TYPE_SHORT

Insertbind [4] .buffer = (char *) & externalPort _ InsertParam-> buffer

Insertbind [4] .is _ null = 0

Insertbind [4] .length = 0

Insertbind [5] .buffer _ type = MYSQL_TYPE_LONG

Insertbind [5] .buffer = (char *) & mresolstInsertParam-> internalIP

Insertbind [5] .is _ null = 0

Insertbind [5] .length = 0

Insertbind [6] .buffer _ type = MYSQL_TYPE_SHORT

Insertbind [6] .buffer = (char *) & internalPort _ InsertParam-> buffer

Insertbind [6] .is _ null = 0

Insertbind [6] .is _ null = 0

/ / bind

If (mysql_stmt_bind_param (m_stInsertStmt, insertbind))

Return false

Return true

}

This is the end of "how to improve the efficiency of MySQL query". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.