Mysql Innodb as Key value store, performance

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:

what could have influenced this result?