2-q: I have a good experience with flat files databases, I started to read a book about mysql optimization and at some point I was upset .
It's not that simple as it seems to be. There is many tricks that need to know and that come with experience, to much options that I don't need.
I just afraid that in some point I will get stuck with it or the performance will be poor at some point.
Also there is few things I don't know how to implement in mysql database , for example:
friends connection, in files i used to write it in dir of each user
top rated items list, in files i just make an index file that read from the top
searches that i make into /ab/cd/ef/de that are super fast as loading a simple page.
and more... | [reply] |
From what you described, I get the impression an SQL optimisation book is not what you need. Rather, try out whether a variant (e.g. SQLite, PostgreSQL) would suit your needs better (i.e. more easily, less painfully) than flat files do.
I don't know the scale nor expected use of your project, so it's hard to advise. SQL isn't that hard, yet it's powerful.
- A friendship (ID) could have two user(ID)s as its members, for instance.
- Top rated items would be selecting an x count of items sorted by their rating values in descending order.
- Indexes are fairly standard too.
Optimising seems more for your flat files design, given that you have more experience there. Using a routine to generate paths from IDs (as suggested earlier) would be a good way to start a benchmark schemes vs filesystem flavour and operating loads you expect to see.
| [reply] |
Well, my own personal experience has led me to develop this one rune: “Do not, if possible, store BLOBs in an SQL database of any kind.” I know that this is an oversimplification; that there must be SQL databases that do this really well and that don’t become an insufferable management PITA. (As the Moody Blues song says, "I know they’re out there some-where ... some-where ... sommmme-where ...”) Whereas, I also know of another commonly-used type of database that is ideally suited for storing tens of millions of variable length records: it’s called “a file system,” and the records are called files. It’s not a good index, but it’s a great store.
Knowing, as I do, that I am about to create perhaps millions of very similarly named files, and also knowing that this “concentration of weight in a small area” might create a PITA of a different sort for the filesystem or for me or both, I would create an additional tree-taxonomy in the form of a directory structure of my own choosing. But, frankly, I would not spend too much time searching for that “sweet spot.” I would hazard that now I am starting to try to meddle in the filesystem’s proper business, probably with no practical return on the time-and-effort investment. Instead, I would simply arrange things so that I can change things later, if I need to.
A more interesting concern might be how to perhaps distribute the data among multiple physical volumes, by assigning different high-level directories to different physical volume-groups. (Assuming of course that I am not paying mega-bucks a month for a system like Oracle.) This taxonomy would let me do that.
| |
I'm learning now data modeling and I loved that relational database model
But why postgresql and not mysql ? is it that bad that no one recommends it ?
| [reply] |