Three Tips for improving MySQL query efficiency page 1/2

Because of its small size and efficient operations, MySQL is increasingly used in database applications. when I was developing a P2P application, I used MySQL to store P2P nodes. Because P2P applications have tens of thousands knots, and nodes change frequently, therefore, you must ensure efficient query and insertion. the following are three effective attempts to improve efficiency during use.

L use statement for binding Query
You can use statement to build a query syntax tree in advance. You do not need to build a syntax tree to directly query a query. therefore, the query efficiency can be improved. this method is suitable for scenarios with fixed query conditions but frequent queries.
The usage is as follows:
Create a MYSQL_STMT variable and bind it to the corresponding query string. The question mark in the string indicates the variable to be passed in. Each question mark must specify a variable.
Query, enter each specified variable, and input the MYSQL_STMT variable to be executed using the available connection handle.
The Code is as follows:

// 1. Bind
Bool CDBManager: BindInsertStmt (MYSQL * connecthandle)
{
// Insert binding
MYSQL_BIND insertbind [FEILD_NUM];
If (m_stInsertParam = NULL)
M_stInsertParam = new CHostCacheTable;
M_stInsertStmt = mysql_stmt_init (connecthandle );
// Construct the BIND 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;
// Fill in 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 *) 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;
// Bind
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 in the 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;
// Execute statement, performance bottleneck
If (mysql_stmt_execute (m_stInsertStmt ))
Return false;
Return true;
}

L random acquisition records
In some database applications, we do not need to obtain all the records that meet the conditions, but just randomly select the records that meet the conditions. this situation is common in the statistical analysis of data services, where a small amount of data is obtained from large-capacity databases.
There are two ways to do this:
1. The conventional method first queries all records that meet the conditions, and then selects some records randomly. This method does not work well when many records meet the conditions.
2. use the limit syntax to obtain the number of records that meet the conditions first, and then add limit to the SQL query statement to query only the records that meet the requirements. although this method needs to be queried twice, it is more efficient when the data volume is large.
The sample code is as follows:

// 1. General Method
// Performance bottleneck. When there are 0.1 million records, the query is performed for 140 ms, and the result set is obtained for 500 ms. Others can be ignored.
Int CDBManager: QueryHostCache (MYSQL * connecthandle, char * channelid, int ISPtype, CDBManager: CHostCacheTable * & hostcache)
{
Char selectSQL [SQL _LENGTH];
Memset (selectSQL, 0, sizeof (selectSQL ));
Sprintf (selectSQL, "select * from HostCache where ChannelID = '% s' and ISPtype = % d", channelid, ISPtype );
If (mysql_real_query (connecthandle, selectSQL, strlen (selectSQL ))! = 0) // search
Return 0;
// Obtain the result set
M_pResultSet = mysql_store_result (connecthandle );
If (! M_pResultSet) // An error occurred while obtaining the result set.
Return 0;
Int iAllNumRows = (int) (mysql_num_rows (m_pResultSet); // <Number of all search results
// Calculate the number of results to be returned
Int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM )? IAllNumRows: RETURN_QUERY_HOST_NUM;
If (iReturnNumRows <= RETURN_QUERY_HOST_NUM)
{
// Obtain records one by one
For (int I = 0; I <iReturnNumRows; I ++)
{
// Obtain fields one by one
M_Row = mysql_fetch_row (m_pResultSet );
If (m_Row [0]! = NULL)
Strcpy (hostcache [I]. sessionid, m_Row [0]);
If (m_Row [1]! = NULL)
Strcpy (hostcache [I]. channelid, m_Row [1]);
If (m_Row [2]! = NULL)
Hostcache [I]. ISPtype = atoi (m_Row [2]);
If (m_Row [3]! = NULL)
Hostcache [I]. externalIP = atoi (m_Row [3]);
If (m_Row [4]! = NULL)
Hostcache [I]. externalPort = atoi (m_Row [4]);
If (m_Row [5]! = NULL)
Hostcache [I]. internalIP = atoi (m_Row [5]);
If (m_Row [6]! = NULL)
Hostcache [I]. internalPort = atoi (m_Row [6]);
}
}
Else
{
// Randomly select a specified record to return
Int iRemainder = iAllNumRows % iReturnNumRows; // <Remainder
Int iQuotient = iAllNumRows/iReturnNumRows; // <Operator
Int iStartIndex = rand () % (iRemainder + 1); // <start subscript
// Obtain records one by one
For (int iSelectedIndex = 0; iSelectedIndex <iReturnNumRows; iSelectedIndex ++)
{
Mysql_data_seek (m_pResultSet, iStartIndex + iQuotient * iSelectedIndex );
M_Row = mysql_fetch_row (m_pResultSet );
If (m_Row [0]! = NULL)
Strcpy (hostcache [iSelectedIndex]. sessionid, m_Row [0]);
If (m_Row [1]! = NULL)
Strcpy (hostcache [iSelectedIndex]. channelid, m_Row [1]);
If (m_Row [2]! = NULL)
Hostcache [iSelectedIndex]. ISPtype = atoi (m_Row [2]);
If (m_Row [3]! = NULL)
Hostcache [iSelectedIndex]. externalIP = atoi (m_Row [3]);
If (m_Row [4]! = NULL)
Hostcache [iSelectedIndex]. externalPort = atoi (m_Row [4]);
If (m_Row [5]! = NULL)
Hostcache [iSelectedIndex]. internalIP = atoi (m_Row [5]);
If (m_Row [6]! = NULL)
Hostcache [iSelectedIndex]. internalPort = atoi (m_Row [6]);
}
}
// Release the result set content
Mysql_free_result (m_pResultSet );
Return iReturnNumRows;
}