Summary of three tips to improve MySQL query efficiency
MySQL is more and more used in database applications because of its small size and efficient operation. I used to develop a peer-to-peer application of the use of MySQL to save Peer-to-peer nodes, due to peer-to-peer applications, knot points are tens of thousands of times, and node changes frequently, So be sure to keep the query and insert efficient. Here are three effective attempts to improve efficiency in the course of my use.
l Use statement to bind queries
The query syntax tree can be built in advance by using statement, and queries are no longer required to construct the syntax tree. Therefore, it can improve the efficiency of query. This method is suitable for situations where the query condition is fixed but the query is very frequent.
The use method is:
Bindings, creates a mysql_stmt variable, binds to the corresponding query string, the question mark in the string represents the variable to pass in, and each question mark must specify a variable.
Query, enter each specified variable, and the incoming mysql_stmt variable is executed with the available connection handle.
The code is as follows:
Copy Code code as follows:
1. Binding
BOOL Cdbmanager::bindinsertstmt (MYSQL * connecthandle)
{
The binding for the insert operation
Mysql_bind Insertbind[feild_num];
if (M_stinsertparam = NULL)
M_stinsertparam = new Chostcachetable;
m_stinsertstmt = Mysql_stmt_init (Connecthandle);
Building a 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 statement associated with the struct variable
memset (insertbind, 0, sizeof (insertbind));
Insertbind[0].buffer_type = mysql_type_string;
Insertbind[0].buffer_length = id_length/* 1 * *;
Insertbind[0].buffer = (char *) m_stinsertparam->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 *) m_stinsertparam->channelid;
Insertbind[1].is_null = 0;
insertbind[1].length = 0;
Insertbind[2].buffer_type = Mysql_type_tiny;
Insertbind[2].buffer = (char *) &m_stInsertParam->ISPtype;
Insertbind[2].is_null = 0;
insertbind[2].length = 0;
Insertbind[3].buffer_type = Mysql_type_long;
Insertbind[3].buffer = (char *) &m_stInsertParam->externalIP;
Insertbind[3].is_null = 0;
insertbind[3].length = 0;
Insertbind[4].buffer_type = Mysql_type_short;
Insertbind[4].buffer = (char *) &m_stInsertParam->externalPort;
Insertbind[4].is_null = 0;
insertbind[4].length = 0;
Insertbind[5].buffer_type = Mysql_type_long;
Insertbind[5].buffer = (char *) &m_stInsertParam->internalIP;
Insertbind[5].is_null = 0;
insertbind[5].length = 0;
Insertbind[6].buffer_type = Mysql_type_short;
Insertbind[6].buffer = (char *) &m_stInsertParam->internalPort;
Insertbind[6].is_null = 0;
Insertbind[6].is_null = 0;
Binding
if (Mysql_stmt_bind_param (m_stinsertstmt, Insertbind))
return false;
return true;
}
2. Query
BOOL Cdbmanager::inserthostcache2 (MYSQL * connecthandle, char * sessionid, char * channelid, int isptype, \
unsigned int eIP, unsigned short eport, unsigned int iIP, unsigned short iport)
{
Fill structure Variable M_sinsertparam
strcpy (M_stinsertparam->sessionid, SessionID);
strcpy (M_stinsertparam->channelid, Channelid);
M_stinsertparam->isptype = Isptype;
M_stinsertparam->externalip = EIP;
M_stinsertparam->externalport = Eport;
M_stinsertparam->internalip = IIP;
M_stinsertparam->internalport = Iport;
Perform statement, performance bottlenecks
if (Mysql_stmt_execute (m_stinsertstmt))
return false;
return true;
}
Current 1/3 page
123 Next read the full text
