I got a few comments about my last example not describing a wormhole, but a whitehole. Time to improve the picture a bit and getting data from another dimension on a shorter route than the long standard way.
To use the picture let's take a look at what has been done:
root@127.0.0.1:test> select * from finance where symbol = "MSFT";
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time | trade_change | open_trade | max_trade | min_trade | volume |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT | 29.84 | 2007-10-05 00:00:00 | 0.13 | 29.84 | 29.99 | 29.73 | 45016520 |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
root@127.0.0.1:test> select * from finance;
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time | trade_change | open_trade | max_trade | min_trade | volume |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT | 29.84 | 2007-10-05 00:00:00 | 0.13 | 29.84 | 29.99 | 29.73 | 45016520 |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
root@127.0.0.1:test> select * from finance where symbol = "YHOO";
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time | trade_change | open_trade | max_trade | min_trade | volume |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| YHOO | 27.8801 | 2007-10-05 00:00:00 | 0.7301 | 27.78 | 28.16 | 27.75 | 28389512 |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
root@127.0.0.1:test> select * from finance;
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| symbol | last_trade | trade_time | trade_change | open_trade | max_trade | min_trade | volume |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
| MSFT | 29.84 | 2007-10-05 00:00:00 | 0.13 | 29.84 | 29.99 | 29.73 | 45016520 |
| YHOO | 27.8801 | 2007-10-05 00:00:00 | 0.7301 | 27.78 | 28.16 | 27.75 | 28389512 |
+--------+------------+---------------------+--------------+------------+-----------+-----------+----------+
There is no UPDATE going on, just SELECTs. But those selects fill the table in the background with live data:
require("curl")
if not c then
c = curl.easy_init()
end
if not res then
res = {}
setmetatable(res, {
-- hmm, in lua each index access returns at least nil
__index = function (t, key)
print("(lua wormhole) looking for key = " .. curl.escape(key))
body = ""
c:setopt(curl.OPT_URL, 'http://download.finance.yahoo.com/d/quotes.csv?f=sl1d1t1c1ohgv&e=.csv&s=' .. curl.escape(key))
c:setopt(curl.OPT_WRITEFUNCTION,
function(str, str_len)
body = body .. str
return str_len, nil
end)
rc, errmsg = c:perform()
if rc ~= 0 then
print("(lua wormhole) query failed: " .. errmsg or "(no errmsg)" )
return nil
end
...
-- cache it so we don't read it again
t[key] = row
return row
end
})
end
return res
We use the primary key specified in the WHERE clause as search-term at yahoo and transform the returned CSV data into the MySQL result row.
As extra bonus we store the data in the result-table to make table-scans aware for the fetched rows. It also speeds up 2nd time access to the same primary key. We cache them.
Next Steps
Now that we have
- table scans
- index reads
we only have UPDATE, INSERT and DELETE and the filesort interface left. For those we will use a new example, perhaps we can find a RESTful webservice we can work against.
Comments