Mysql Innodb as Key value store, performance

MySQLmysql-5.6

I read about NoSQL databases using Key value pairs (KVP) tables. So i wanted to find out if KVP tables would be faster than conventional relational tables (with multiple columns) in mysql with innodb engine.

So I created a database table with an auto increment primary key and 12 other columns (firstname,lastname etc). All string values except the primary key. I filled it up with 500.000 records (random strings).

Now i created another database and a table but only with 2 columns: Key and value. Now i converted all the records of the conventional database to the KVP principle so row(1. john) becomes 000001_firstname = john throug queries like INSERT INTO kvp (SELECT CONCAT(LPAD(id, 8, "0"),"_firstname"),first_name FROM datatable);. This resulted in a table with 6 million records.

Now i disabled query caching and other caching and i ran the following query on the conventional table: "SELECT id FROM datatable WHERE first_name = 'Ehm'"
and the following query on the kvp table: "SELECT id FROM datatable WHERE key like '%firstname' and value = 'Ehm'"

I tested the speed of this query doing 100 iterations which resulted in 0.4 seconds for the kvp table but 20 seconds for the conventional table (with a flush tables after each query).

I used a php-script for testing:

public function benchmark($query,$dbName){
    $db = new mysqli("localhost", "root", null, $dbName);
    $db->set_charset("utf8");
    $time = microtime(true);
    for($i=0; $i<$this->iterations; $i++){
        mysqli_query($db,$query);
        mysqli_query($db,'flush tables;');
    }
    $duration = microtime(true)-$time;
    $db->close();
    return $duration;
}

how is this speed difference possible?

  • i initialized both tables the same way
  • disabled query caching
  • i did not put an index on value column of kvp table.

same result with the query: "SELECT id FROM datatable WHERE first_name like '%ad%'"

i tried to find if there was a fault in my testingscript but i could not find anny. Furthermor i tried to find literature explaining this but i could not find anny. I use mysql 5.6 and somehwere i read mysql uses memcache since 5.6, maybe this has influenced the result?

This is the result i got:
difference key value storage in performance

what could have influenced this result?

Best Answer

Joel, there is a lot of unknowns. If you post your my.cnf, queries, table definitions and queries, EXPLAIN plan output, then you might get more precise answers.

You say "disabled query caching and other caching" what 'other caching' are you referring to? What steps did you take to disable QC? Did you restart your MySQL server between tests to flatten any buffers? If no and the KVP table was smaller and fit into RAM then InnoDB might have been able to serve queries fitting a certain criteria from data in the innodb buffer pool.

You also refer to memcache. With MySQL 5.6 there is a memcache api. this means you can use memcache calls to access data stored in innodb. This will not be incidentally speeding up your queries. There is also another method of bypassing the SQL layer within MySQL called HandlerSocket. These are a couple of subjects that I would point you at should you continue to desire this type of access to your data with the added bonus of having MySQL persist the data to disk and also be crash safe through InnoDB's crash recovery process.

Related Question