MySQL in C++ running slowly

We have MySQL database. We are trying to connect to this MySQL database with ODBC drivers in C++ code. The ODBC drivers used by us is with version 3.51.

The database structure is as follows: We have a conversation table CONV_X1 in database D1, that contains the IP address of client and server machines and the data corresponding to this conversation in the following format:

ProtocolID ServerID ClientID TimeStamp InOctets OutOctets

     24  167772161  167772162  1310121900         0  1.741e+006 
    632  167772161  167772162  1310121900         0  1.741e+006 
     24  167772161  167772162  1310122800         0    1.8e+006 

We have another table PROT_NAME in another database D2 that has information for this protocol ID. We are mapping ProtocolID from CONV_X1 table in database D1 with PROT_NAME table in database D2 to get the protocol name.

From information_schema the table name CONV_X1 is taken as there can be multiple tables with the same name in D1 with different ID(example. CONV_X1, CONV_X2 etc.)

We are extracting data from this database in the format

ServerID:ClientID_ProtocolName_InOctets_tableName
ClientID:ServerID_ProtocolName_InOctets_tableName 

so, for 1310121900 timestamp the data we are manipulating is in following format

10.0.0.1:10.0.0.2_ftp_InOctets_CONV_X1 data is 0
10.0.0.2:10.0.0.1_ftp_InOctets_CONV_X1 data is 1.741e+006

10.0.0.1:10.0.0.2_udp_InOctets_CONV_X1 data is 0
10.0.0.2:10.0.0.1_udp_InOctets_CONV_X1 data is 1.741e+006

We are using the following query to achieve the result

select distinct concat(inet_ntoa(np.serverid), ':',inet_ntoa(np.clientid),'_inoctets_',rp.name,'_',c.table_name)HostCounter1, concat(inet_ntoa(np.clientid),':',inet_ntoa(np.server开发者_运维技巧id),'_inoctets_',rp.name,'_',c.table_name)HostCounter2, np.inoctets value1 ,np.outoctets value2, from_unixtime(timestamp) from information_schema.`COLUMNS` c,D1.CONV_X1 np,D2.PROT_NAME rp where c.table_schema='D1' AND c.table_name ='CONV_X1' AND np.protocolid=rp.id AND np.timestamp between unix_timestamp('2011-08-31 10:33:14') AND unix_timestamp('2011-08-31 11:33:14') order by timestamp;

This will give output in following format:

HostCounter1 HostCounter2 value1 value2 from_unixtime(timestamp)
10.0.0.1:10.0.0.2_ftp_InOctets_CONV_X1 10.0.0.2:10.0.0.1_ftp_InOctets_CONV_X1 0 1.741e+006 1310121900(in unix format)
10.0.0.1:10.0.0.2_udp_InOctets_CONV_X1 10.0.0.2:10.0.0.1_udp_InOctets_CONV_X1 0 1.741e+006

Our C++ code executes this using SQL calls SQLDriverConnect(), SQLExecDirect(), these calls are taking a lot of time to connect to database and execute this query. Due to this we are facing performance issue with our C++ code. Please let us know is there any way to resolve the performance issue. Will the change in ODBC driver can help us? Can we optimize the query? If so how?

Try looking at the query plan and ensure that you have the appropriate indexes.