[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re[2]: [GNUnet-developers] MYSQL optimization
From: |
Hendrik Pagenhardt |
Subject: |
Re[2]: [GNUnet-developers] MYSQL optimization |
Date: |
Tue, 16 Dec 2003 10:26:20 +0100 |
> But of course the UNION is not critical here, it can be
> done by two subsequent queries where the latter is performed
> only if the first returns an empty set. Duh. Feeling a bit
> tired, thats all...
OK, here it comes:
-----------------
int getRandomContent(HighDBHandle handle,
ContentIndex * ce) {
mysqlHandle * dbh = handle;
MYSQL_RES * sql_res;
MYSQL_ROW sql_row;
char * escapedHash;
char * hash;
char * scratch;
int i;
int found;
MUTEX_LOCK(&dbh->DATABASE_Lock_);
hash = MALLOC(sizeof(HashCode160));
escapedHash = MALLOC(2*sizeof(HashCode160)+1);
scratch = MALLOC(2*sizeof(HashCode160)+256+1);
found = NO;
for (i=0;i<sizeof(HashCode160);i++)
hash[i] = randomi(256);
mysql_escape_string(escapedHash, hash, sizeof(HashCode160));
sprintf(scratch,
"SELECT hash,type,priority,fileOffset,fileIndex "
"FROM data%uof%u "
"WHERE hash >= '%s' "
"AND (type = %d OR type = %d) "
"LIMIT 1",
dbh->n,
dbh->i,
escapedHash,
LOOKUP_TYPE_CHK,
LOOKUP_TYPE_CHKS);
mysql_query(dbh->dbf, scratch);
if(mysql_error(dbh->dbf)[0]) {
LOG(LOG_ERROR,
"ERROR: %s\n",
mysql_error(dbh->dbf));
FREE(scratch);
FREE(escapedHash);
FREE(hash);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
return SYSERR;
}
if(!(sql_res=mysql_store_result(dbh->dbf))) {
LOG(LOG_ERROR,
"ERROR: %s\n",
mysql_error(dbh->dbf));
FREE(scratch);
FREE(escapedHash);
FREE(hash);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
return SYSERR;
}
if (mysql_num_rows(sql_res)==0) {
mysql_free_result(sql_res);
sprintf(scratch,
"SELECT hash,type,priority,fileOffset,fileIndex "
"FROM data%uof%u "
"WHERE hash >= '' "
"AND (type = %d OR type = %d) "
"LIMIT 1",
dbh->n,
dbh->i,
LOOKUP_TYPE_CHK,
LOOKUP_TYPE_CHKS);
mysql_query(dbh->dbf, scratch);
if(mysql_error(dbh->dbf)[0]) {
LOG(LOG_ERROR,
"ERROR: %s\n",
mysql_error(dbh->dbf));
FREE(scratch);
FREE(escapedHash);
FREE(hash);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
return SYSERR;
}
if(!(sql_res=mysql_store_result(dbh->dbf))) {
LOG(LOG_ERROR,
"ERROR: %s\n",
mysql_error(dbh->dbf));
FREE(scratch);
FREE(escapedHash);
FREE(hash);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
return SYSERR;
}
}
if(mysql_num_rows(sql_res)>0) {
if(!(sql_row=mysql_fetch_row(sql_res))) {
LOG(LOG_ERROR,
"ERROR: %s\n",
mysql_error(dbh->dbf));
FREE(scratch);
FREE(escapedHash);
FREE(hash);
mysql_free_result(sql_res);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
return SYSERR;
}
memcpy(&ce->hash,
sql_row[0],
sizeof(HashCode160));
ce->type = htons(atol(sql_row[1]));
ce->importance = htonl(atol(sql_row[2]));
ce->fileOffset = htonl(atol(sql_row[3]));
ce->fileNameIndex = htons(atol(sql_row[4]));
found = YES;
mysql_free_result(sql_res);
}
FREE(scratch);
FREE(escapedHash);
FREE(hash);
MUTEX_UNLOCK(&dbh->DATABASE_Lock_);
if(found==YES) {
return OK;
} else {
LOG(LOG_DEBUG,
"DEBUG: MySQL random didn't find anything!\n");
return SYSERR;
}
}
----------------------
As promised: more LOC ;-), but seems to work. I'd be glad to hear if it
works for other people, too.
Igor, can you have a look at it? If it works ok for you too, maybe it
can go into the CVS?
Ciao,
Hendrik