Re[2]: [GNUnet-developers] MYSQL optimization

[Hendrik Pagenhardt]

> 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