20% to 50% improvement in MariaDB 5.3 Handler Interface using prepared statement

handler t1 open;
prepare stmt from ‘handler t1 read a=(?)’;
set @a=1000;
execute stmt using @a;
deallocate prepare stmt;

#include <iostream>
#include <cstdlib>
#include <cstring>
#include <cstdio>
#include “mysql.h”
using namespace std;

int main(int argc, char *argv[])
{
cout << “start up” << endl;
MYSQL * connection;
connection = mysql_init(0);
connection = mysql_real_connect(connection, 0, “stephane”, “”, “test”, 0,
“/tmp//mysql.sock”, 0);
if (0 == connection)
{
cerr << “connect failed:” << mysql_error(connection) << endl;
return -1;
}
static const char * sql_statement =”HANDLER login READ `PRIMARY`=(?)”;
MYSQL_STMT * stmt_handle = mysql_stmt_init(connection);
if (0 == stmt_handle)
{
cerr << “stmt init failed:” << mysql_error(connection) << endl;
return -1;
}
int mysql_return_code = mysql_stmt_prepare(stmt_handle,
sql_statement,
strlen(sql_statement)
);
if (0 != mysql_return_code)
{
cerr << “stmt prepare failed:” << mysql_stmt_error(stmt_handle) <<
endl;
mysql_stmt_close(stmt_handle);
return -1;
}

MYSQL_BIND bind_var[1];
unsigned long var_length[3];
my_bool not_null = 0;
int int_data;

bind_var[0].buffer_type = MYSQL_TYPE_LONG;
bind_var[0].buffer = (char *) &int_data;
bind_var[0].length= 0;
bind_var[0].is_null = 0;

mysql_return_code = mysql_stmt_bind_param(stmt_handle, bind_var);
if (0 != mysql_return_code)
{
cerr << “stmt bind failed:” << mysql_stmt_error(stmt_handle) << endl;
mysql_stmt_close(stmt_handle);
return -1;
}
int_data= 1000;
int user_id;
char user_name[50] = {‘\0′};
char user_email[255] = {‘\0′};
MYSQL_TIME created;
MYSQL_BIND result_var[4];
unsigned long result_var_length[4];
my_bool is_null[1];

for (int i=0; i<1000000; ++i)
{
mysql_return_code = mysql_stmt_execute(stmt_handle);
if (0 != mysql_return_code)
{
cerr << “stmt execute failed:” << mysql_stmt_error(stmt_handle) <<
endl;
mysql_stmt_close(stmt_handle);
return -1;
}
bzero(result_var, sizeof(result_var));
result_var[0].buffer_type= MYSQL_TYPE_LONG;
result_var[0].buffer= (char *)&user_id;
result_var[0].is_null= &is_null[0];
result_var[0].length= &result_var_length[0];
result_var[1].buffer_type = MYSQL_TYPE_VAR_STRING;
result_var[1].buffer = (char *)user_name;
result_var[1].buffer_length = sizeof(user_name)-1;
result_var[1].is_null = &is_null[1];
result_var[1].length = &result_var_length[1];
result_var[2].buffer_type = MYSQL_TYPE_VAR_STRING;
result_var[2].buffer = (char *)user_email;
result_var[2].buffer_length = sizeof(user_email)-1;
result_var[2].is_null = &is_null[2];
result_var[2].length = &result_var_length[2];
result_var[3].buffer_type = MYSQL_TYPE_DATETIME;
result_var[3].buffer = (char *)&created;
result_var[3].is_null = &is_null[3];
result_var[3].length = &result_var_length[3];

mysql_return_code = mysql_stmt_bind_result(stmt_handle, result_var);
if (0 != mysql_return_code)
{
cerr << “stmt bind rslt failed:” << mysql_stmt_error(stmt_handle) <<
endl;
mysql_stmt_close(stmt_handle);
return -1;
}

mysql_return_code = mysql_stmt_store_result(stmt_handle);
if (0 != mysql_return_code)
{
cerr << “stmt store rslt failed:” << mysql_stmt_error(stmt_handle) <<
endl;
mysql_stmt_close(stmt_handle);
return -1;
}

mysql_return_code = mysql_stmt_fetch(stmt_handle);
if (0 != mysql_return_code && MYSQL_NO_DATA != mysql_return_code)
{
cerr << “stmt fetch failed:” << mysql_stmt_error(stmt_handle) << endl;
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
return -1;
}

if (MYSQL_NO_DATA != mysql_return_code)
{
//  cout << “stmt – got: ” << user_name << endl;
}
} //end for
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
mysql_close(connection);
mysql_server_end();
cout << “done” << endl;
return 0;
}