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.

In your database, do you have indices on columns ticker, whichprice and date?
–
Gustavo DelfinoJan 26 '12 at 22:06

1

How long does the exact same query take in MySQL itself (command line interface or one of the basic MySQL clients out there)?
–
Arnoud BuzingJan 26 '12 at 22:13

1

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.
–
Michael SternJan 26 '12 at 22:18

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?
–
Michael SternJan 26 '12 at 22:56

3 Answers
3

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.

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.

Mathematica is a registered trademark of Wolfram Research, Inc. While the mark is used herein with the limited permission of Wolfram Research, Stack Exchange and this site disclaim all affiliation therewith.