//1. Conventional methods
// Performance bottleneck, when 100,000 records are executed, the query is executed for 140ms, the result set is obtained for 500ms, the rest 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(connectthandle, selectSQL, strlen(selectSQL)) != 0) //Retrieve
return 0;
//Get the result set
m_pResultSet = mysql_store_result(connecthandle);
if(!m_pResultSet) //An error occurred to get the result set
return 0;
int iAllNumRows = (int)(mysql_num_rows(m_pResultSet)); ///<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)
{
//Get records by item
for(int i = 0; i<iReturnNumRows; i++)
{
//Get field by field
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 the specified records and return
int iRemainder = iAllNumRows%iReturnNumRows; ///<Remaining
int iQuotient = iAllNumRows/iReturnNumRows; ///<Business
int iStartIndex= rand()%(iRemainder + 1); ///<Start subscript
//Get records by item
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;
}
//2.Use limit version
int CDBManager::QueryHostCache(MYSQL * connecthandle, char * channelid, unsigned int myexternalip, int ISPtype, CHostCacheTable * hostcache)
{
//First get the number of records that meet the results, and then use limit to randomly select the specified records to return.
MYSQL_ROW row;
MYSQL_RES * pResultSet;
char selectSQL[SQL_LENGTH];
memset(selectSQL, 0, sizeof(selectSQL));
sprintf(selectSQL,"select count(*) from HostCache where ChannelID = '%s' and ISPtype = %d", channelid, ISPtype);
if(mysql_real_query(connectthandle, selectSQL, strlen(selectSQL)) != 0) //Retrieve
return 0;
pResultSet = mysql_store_result(connecthandle);
if(!pResultSet)
return 0;
row = mysql_fetch_row(pResultSet);
int iAllNumRows = atoi(row[0]);
mysql_free_result(pResultSet);
//Calculate the upper and lower ranges of records to be retrieved
int iLimitLower = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
0:(rand()%(iAllNumRows - RETURN_QUERY_HOST_NUM));
int iLimitUpper = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
iAllNumRows:(iLimitLower + RETURN_QUERY_HOST_NUM);
//Calculate the number of results to be returned
int iReturnNumRows = (iAllNumRows <= RETURN_QUERY_HOST_NUM)?
iAllNumRows:RETURN_QUERY_HOST_NUM;
//Use limit for query
sprintf(selectSQL,"select SessionID, ExternalIP, ExternalPort, InternalIP, InternalPort "
"from HostCache where ChannelID = '%s' and ISPtype = %d limit %d, %d"
, channelid, ISPtype, iLimitLower, iLimitUpper);
if(mysql_real_query(connectthandle, selectSQL, strlen(selectSQL)) != 0) //Retrieve
return 0;
pResultSet = mysql_store_result(connecthandle);
if(!pResultSet)
return 0;
//Get records by item
for(int i = 0; i<iReturnNumRows; i++)
{
//Get field by field
row = mysql_fetch_row(pResultSet);
if(row[0] != NULL)
strcpy(hostcache[i].sessionid, row[0]);
if(row[1] != NULL)
hostcache[i].externalIP = atoi(row[1]);
if(row[2] != NULL)
hostcache[i].externalPort = atoi(row[2]);
if(row[3] != NULL)
hostcache[i].internalIP = atoi(row[3]);
if(row[4] != NULL)
hostcache[i].internalPort = atoi(row[4]);
}
//Release the result set content
mysql_free_result(pResultSet);
return iReturnNumRows;
}