I have a database (MySQL 5.1 at Ubuntu 10.10) with some 15000 tables each with ~1 000 000 rows on average. Each table has 6 DOUBLE columns. The storage engine is MyISAM. I have a C++ application that loads the data one table at a time and performs some calculations.
The way I retrieve the data from the database is simply by: SELECT * FROM table ORDER BY timestamp; (timestamp is the first column (DOUBLE) marked as UNIQUE)
By far most of the time is spent in loading and fetching. It takes ~15s to load and fetch all the rows in one table (tried with the native C API, C++ Connector and MySQL Query Browser).
When I load the same dataset from disk (plain text file) using fstream the same operation takes only ~4s.
Is it possible for MySQL to get anywhere near this value?
P.S. No optimisations (server/tables) attempted so far.
P.S. Tables.pdf attached to this post contains some statistics from PROCEDURE ANALYSE.