MariaDB [imdb]> show status like "Innodb_rows_read";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| Innodb_rows_read | 1237459 |
+------------------+---------+
1 row in set (0.00 sec)
MariaDB [imdb]> select x.* from entity as orig, entity as dest, imdb_graph, entity as x where dest.type='ACTOR' and dest.name='Kevin Bacon' and imdb_graph.latch=2 and imdb_graph.origid=orig.id and imdb_graph.destid=dest.id and x.id=imdb_graph.linkid and orig.type='ACTOR' and orig.name='James Earl Jones';
+---------+---------+------------------------------------------------------------------+
| id | type | name |
+---------+---------+------------------------------------------------------------------+
| 780890 | ACTOR | James Earl Jones |
| 1922997 | ARCHIVE | "20 to 1" (2005) {Our All Time Favourite Films (#9.11)} (voice) |
| 131965 | ACTOR | Antonio Banderas |
| 1945521 | ARCHIVE | Oscar, que empiece el espect?culo (2008) |
| 1003551 | ACTOR | Kevin Bacon |
+---------+---------+------------------------------------------------------------------+
5 rows in set (2.80 sec)
MariaDB [imdb]> show status like "Innodb_rows_read";
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| Innodb_rows_read | 2102482 |
+------------------+---------+
1 row in set (0.00 sec)
MariaDB [imdb]> select (2102482 - 1237459)/2.8;
+-------------------------+
| (2102482 - 1237459)/2.8 |
+-------------------------+
| 308936.7857 |
+-------------------------+
1 row in set (0.09 sec)
Over 300,000 edges examined per second... Nifty.
The entity table has approximately 3 million entries and the link table has approximately 30 million edges.
Note - this performance is with a warmed up buffer pool. With a cold buffer pool, the query takes nearly 30 seconds.
