I often use Mathematica and MySQL together; it’s typically a lot more efficient than trying to keep megabytes of data in the Mathematica notebook itself, and becomes vastly more powerful if I want to use the same data from multiple machines, all of which can tap into the same database.
Mathematica supports these uses natively, and you can send arbitrary SQL code to the server with the SQLExecute command. Interestingly, Mathematica also provides commands that map to major SQL commands, including SQLSelect[] and SQLInsert[]. I have never used these, viewing it as easier to program SQL commands in SQL itself, tuning them in MySQL Workbench.
Today, it occurred to me that Mathematica’s built-in commands might somehow be quicker than sending raw SQL, and I tested this hypothesis. Surprisingly, it turns out to be the case, with results similar to the following for a number of cases.
In[1]:= Needs["DatabaseLink`"]
In[2]:= commodDB = OpenSQLConnection[JDBC["MySQL(Connector/J)", "localhost:3306/commoddb"],
Username -> "myUsername", Password -> "myPassword"];
In[3]:= Timing[
SQLSelect[commodDB, "tbcommodprices", {"date", "ticker", "price"},
SQLColumn["ticker"] == "LAV08 Comdty" && SQLColumn["whichprice"] == "last"];]
Out[3]= {0.031, Null}
In[4]:= Timing[
SQLExecute[commodDB, "SELECT date, ticker, price FROM `commoddb`.`tbcommodprices` where ticker='LAV09 Comdty' and whichprice='last'"];]
Out[4]= {0.047, Null}
(The two queries differ slightly because I did not want caching to give an advantage to the second query).
