In this post we’ll describe a query that accrued significant performance advantages from using a relatively long index key. (This posting is by Zardosht and Bradley.)
We ran across this query recently when interacting with a customer (who gave us permission to post this sanitized version of the story):
|
1 |
<br>SELECT name,<br> Count(e2) AS CountOfe2<br> FROM (SELECT distinct name, e2<br> FROM (SELECT atable.NAME AS name,<br> pd1.NAME AS e2<br> FROM atable INNER JOIN atable AS pd1<br> ON (atable.id = pd1.id)<br> AND (atable.off = pd1.off)<br> AND (atable.len = pd1.len)) ent<br> WHERE ((ent.name<>ent.e2))) outside<br> GROUP BY outside.name order by CountOfe2 desc;<br> |
With a table defined as
|
1 |
<br>CREATE TABLE `atable` (<br> `id` varchar(25) DEFAULT NULL,<br> `off` bigint(20) DEFAULT NULL,<br> `len` bigint(20) DEFAULT NULL,<br> `name` varchar(1000) DEFAULT NULL,<br> `x` bigint(20) DEFAULT NULL,<br> `y` bigint(20) DEFAULT NULL,<br> `pid` varchar(25) DEFAULT NULL,<br> `typ` varchar(25) DEFAULT NULL,<br> KEY `nameIDX` (`name`),<br> KEY `typIDX` (`typ`),<br> KEY `idIDX` (`id`),<br> KEY `lenIDX` (`len`),<br> KEY `offIDX` (`off`),<br> KEY `pidIDX` (`pid`)<br> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;<br> SET character_set_client = @saved_cs_client;<br> |
The actual table has about 30,000 rows, so it fits in main memory.
We concluded that with an index better suited for this query, we can speed up this query using either MyISAM and TokuDB’s storage engine for MySQL. However, TokuDB’s indexing flexibility provides about a 3x performance advantage over MyISAM.
All experiments were run on a Core 2 duo 2.4Ghz machine with 3GB RAM running Linux.
Here is what we did.
First, we ran the query as-is, to see the difference between TokuDB and MyISAM:
Next, we added an index to atable to make the inner join run faster. For TokuDB, we added a covering index comprising (id, off, len, name). For MyISAM, we added an index comprising (id, off, len), which covers the predicate, but not the whole query. We could not add name to MyISAM’s index, because MyISAM’s key size limit would not allow it. The customer had defined the length of the name field, so shortening it didn’t seem feasible. The new query times were:
which helped MyISAM a lot, and helped TokuDB a little.
To get more speedup, we observed that this query generates two temporary tables for the following parts:
These temporary tables do not have a good index of (name, e2). If the temporary tables had this index, we could speed up the query even further. To do this, we transformed the query into:
|
1 |
<br>create temporary table tmp_prox (name varchar(1000), e2 varchar(1000), key (name, e2))engine=tokudb;<br>create temporary table tmp_prox2 (name varchar(1000), e2 varchar(1000), key (name, e2))engine=tokudb;<br>insert into tmp_prox ( SELECT atable.NAME AS name,<br> pd1.NAME AS e2<br> FROM atable INNER JOIN atable AS pd1<br> ON (atable.id = pd1.id)<br> AND (atable.off = pd1.off)<br> AND (atable.len = pd1.len) );<br>insert into tmp_prox2 ( SELECT distinct name, e2 from tmp_prox where ((name<>e2)) );<br>SELECT name, Count(e2) AS CountOfe2 FROM tmp_prox2 GROUP BY name order by CountOfe2 desc;<br> |
We could not do this with MyISAM, because the index (name, e2) exceeded MyISAM’s maximum index size. Running these statements with TokuDB took 4.9 seconds.
Thus, a covering index speeds up MyISAM by a factor of 5.7, and TokuDB’s indexing flexibility provided an additional 3x speedup. This transformation isn’t really specific to TokuDB. TokuDB really differentiates itself from other storage engines when maintaining indexes that don’t fit in main memory. This table fits in main memory, so transformation would probably work on some other storage engines that accept bigger index key lengths than does MyISAM. When the table does get bigger, TokuDB’s Fractal Tree® indexes can create and maintain those various indexes on cheap hardware even at high data arrival rates.
