I've got a (local) database (MySQL 5.1 at Ubuntu 10.10) with a few 15000 tables each with ~1 000 000 rows normally. Each table has 6 DOUBLE posts. The storage engine is MyISAM. I've got a C++ application that loads the information one table at any given time and works some information. Generate an income retrieve the information in the database is actually by: Choose * FROM table ORDER BY timestamp (timestamp may be the first column (DOUBLE) marked as UNIQUE) Undoubtedly more often than not is put in loading and fetching. It requires ~15s to load and fetch all of the rows in a single table (attempted using the native C API, C++ Connector and MySQL Query Browser). After I load exactly the same dataset from disk (plain text file) using fstream exactly the same operation takes only ~4s.
Is it feasible for MySQL or other database (SQLite?) to obtain anywhere near this value? Although, I've mostly simple Chooses and Card inserts (+ one easy JOIN) I like the thought of database since it is somewhat simpler to handle large data sets, and so i would stick to it even at price of some performance loss, but 15/4s per table is a significant amount of given the amount of tables. I'd be fine with 6/4s though...
Reading through personal files is different then using SQL to fetch the information. Reading through personal files only involves reading through in the disk and putting it into memory. Thats it.
Now, using SQL to fetch structured data, now thats different. First, MySQL needs to parse the query and structure it therefore it can carry it out and browse the information. When performing the query, MySQL opens the database file and reads some meta data associated with that database.
Then, when that's done, it parses the file and brings the information based on the query. There's additionally a small overhead since the communication between client and server is performed via. electrical sockets.
So, there's a huge distinction between file access and what MySQL does. With MySQL you receive much, much more, at the expense of speed.
Why do you want 15 000 tables anyway? I sense a flaw inside your design if you want a lot of tables...
Consecutive scan of records is not precisely the most convincing use situation for any relational database, however i certainly would encourage you to definitely benchmark SQLite too. It's generally regarded as a higher performance alternative for custom file I/O.
If performance is of absolute concern, you may also test out mmap. This enables you to possess a disk-backed memory area, taking advantage of perfectly enhanced virtual memory and caching code.
I have seen a credit card applicatoin (utilized in a significant social media site) that, for any very specific need, changed a cluster of 8 large MySQL servers with enhanced C++ code running on one edge at ~5-10% utilization. (It calculated the social graph and least pathways between customers).
Generally, you finish up having to pay for that generalized solution. Analyse your requirements carefully, apply algorithmic understanding, then choose your weapon.. Many designers result in the mistake of selecting the things they know, then attempting to fudge calculations in it, and lastly being careful from the needs.
First of all, you are absuing the database fairly badly by getting 15,000 tables. This isn't how these databases usually are meant to work.
Next, any client-server database will probably need several copy procedures in memory, that will impose an maximum around the speed even if the information happen to be in memory. Something similar to sqlite may avoid (a number of) these copies by utilizing data from the buffer.
You are utilizing a SQL database for something it isn't meant for - and mistreating it, at this. I would not expect it to perform a excellent job.