11
$\begingroup$

I have an application that queries a MySQL database many times for data. At the moment, Mathematica and MySQL are running on the same box and yet it takes over 1.6 seconds for the query to run and return a value. This feels very slow, and I was hoping somebody might give me counsel on how this might be sped up.

AbsoluteTiming[
 SQLSelect[commodDB, "tbcommodprices", "price",  
   SQLColumn["ticker"] == "LAV05 Comdty" && 
    SQLColumn["whichprice"] == "last" && 
    SQLColumn["date"] == "2005-09-01"];]

{1.6093750, Null}
|improve this question
$\endgroup$
  • $\begingroup$ In your database, do you have indices on columns ticker, whichprice and date? $\endgroup$ – Gustavo Delfino Jan 26 '12 at 22:06
  • 1
    $\begingroup$ How long does the exact same query take in MySQL itself (command line interface or one of the basic MySQL clients out there)? $\endgroup$ – Arnoud Buzing Jan 26 '12 at 22:13
  • 1
    $\begingroup$ Regarding running in MySQL itself: This is a great question. I have just checked and it takes 1.6-1.7 seconds. It looks like this is going to require speeding things up in MySQL, not Mathematica. Regarding indices: I don't think so. ticker is just a VARCHAR, price is a DOUBLE, and date is a DATE. Each record has an INTEGER key. There are about 909,000 records in the relevant table. $\endgroup$ – Michael Stern Jan 26 '12 at 22:18
  • $\begingroup$ Following the hint of gdelfino and the DBAs over at dba.stackexchange.com, I created an index for this table with ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date); That sped up my queries by a factor of 27x. Does anybody know if this index needs to be updated manually, or will the server take care of it automatically? $\endgroup$ – Michael Stern Jan 26 '12 at 22:56
  • $\begingroup$ Looks like you solved your problem. $\endgroup$ – Leonid Shifrin Jan 26 '12 at 23:01
5
$\begingroup$

Adding a database index is very important when SELECTing data from a big table. Once you add the index, MySQL will take care of keeping it updated. The disadvantage of indices is that your database now takes more space in your HD and that your INSERTs are now slower.

Note that you created a multiple-column index that speeds up you SELECTs when you lookup by:

  • ticker, whichprice and date
  • ticker and whichprice
  • ticker

If you do a SELECTs by whichprice or date or (whichprice and date) will still be slow.

Lastly, if this is a read-only table you could use the MyISAM storage engine and maybe even move to MariaDB (a mysql fork) for better performance.

|improve this answer
$\endgroup$
  • $\begingroup$ Thanks for the tip about MariaDB! $\endgroup$ – nilo de roock Feb 1 '12 at 16:04
7
$\begingroup$

The answer here is found in the comments above:

The MySQL query itself takes 1.6-1.7 seconds, so the time spend in Mathematica on this query is negligible and can not realistically be optimized in this case.

|improve this answer
$\endgroup$
  • $\begingroup$ I was too quick with my suggestions, in retrospect this certainly is the first and most natural thing to try / check. +1. $\endgroup$ – Leonid Shifrin Jan 27 '12 at 9:12
3
$\begingroup$

It looks like you have the answer by added indexes. Note you can check how MySql is satisfying your query using the keyword explain . This will tell you if there are any full table scan going on which will slow down the query enormously depending on the size of the tables.

See here for more info.

|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.