Phases of Database Growth and Cost

Here's some of what's not supported:

  Stored procedures and functions
  Triggers
  Events
  User-defined functions
  FOREIGN KEY constraints #18209
  FULLTEXT syntax and indexes #1793
  SPATIAL (also known as GIS/GEOMETRY) functions, data types and indexes #6347
  Character sets other than ascii, latin1, binary, utf8, utf8mb4, and gbk.
  SYS schema
  Optimizer trace
  ...

Then again, at a certain scale you kind of have to look into alternative ways of how to look at working with your data, so there's that.

That said, the idea of giving people something that's compatible (for the most part) with the drivers and syntax that they already utilize is an excellent one!

Picking boring tech for crucial areas of a business is almost always the best plan.

https://en.m.wikipedia.org/wiki/Drizzle_(database_server)

That's the dream, but often the answer is "no, not even sort of".

Plenty of companies with super smart and experienced engineers laying the DB foundations for a company early in its life have failed at this goal. A few problems come into effect, two immediately spring to mind:

1. Eventually some engineer comes along with a reasonably compelling business case to either pass by the abstraction layer, or extend it so that it's no longer general (e.g. now it only works for Postgres).

2. As the company scales Hyrum's law comes into effect and a thousand pieces of code come to have subtle dependencies on implementation details of the DB.

(2) is particularly hard to avoid because DBs are often at least the nominal source of performance bottlenecks and engineers will do almost anything to make performance acceptable no matter how horribly it wrecks abstraction layers.

* ORM bypass, for better performance. * Sharding and application level handling of sharding (making DB not transparent) * Replication (and obviously, delays)

And there are always tons of application level code just written because DB just cannot do it efficiently.

Unless you're building a piece of software where database agnosticism is actually a required feature, then I generally find it to be a very poor tradeoff. Databases are incredibly powerful, you should be using them to their fullest extent rather than purposefully handicapping yourself.

As I refactored the library over the years, I realized more and more that increasing the scope and encompassing an entire content management system actually made things easier (still retaining the ability to INCLUDE any custom php file), as I could do the entirety of every web page in a single MySQL connection (open the database connection as the start of the page, doing the myriad queries the page required, then close the connection at the end of the page). In the general case, MySQL typically spends a lot more time making connections that it does executing queries.

And then I realized the bulk of my SELECT statements were used to create grid tables from large data sets. Standard data grid table with CRUD + search + sort capabilities on every column and every row.

I took a look at this from first principles and realized if I split the queries into two (one for the rows and another for the columns), I could eliminate the ugly IFNULL(MAX(CASE ... statements. In other words, you create one loop just pulling out the id for each row and inside that loop call another loop for all the grid columns based on that id needed to display that row. Assuming a grid of 30 rows with 20 columns say, that is 21 queries (but all over a single database connection). Of course you manage all the grid tables with pagination, so you might have 10,000 grid pages displaying 300,000 records 30 at a time on each page. If you present those grid tables in a usable manner (sorted by records most frequently accessed/updated, most recently accessed/updated etc), then statistically most of your users stay on the first grid page. And you manage the LIMIT statement to only ever get the 30 "virtual" records on the current page, whether in PHP or in JSON. Pretty fast!

As far as indexing is concerned, well each of the 7 real MySQL tables was indexed, so yeah EVERY field is indexed whether you want it or not. Creating combined indexes between tables has not been attempted.

So I got that approach performing nicely, then took it to another level putting the database schema itself inside the database. I could now build a "virtual" table from combining as many of the 7 fields as required for any given table definition. And just like a real MySQL table, you could join multiple "virtual" tables. After all an EAV database is about as normalized as you are ever going to get (one field per table, plus 2 indexes). Of course this is all done in data, so imagine a standard web table grid being used to Create/Retrieve/Update/Delete all the "virtual" table field definitions, labels, css values, etc..

This approach satisfied two of my business requirements - one need was to have a single framework to support a small number of clients with large datasets (e.g. millions of records, hundreds of tables). For them, I now had a tool where you do not need to be a programmer to create a simple web table grid of data. And where they needed hand-holding on more complex requirements, solutions could be deployed in a fraction of the time it would have taken me using traditional methods.

The other need was to support a large number of clients (thousands) with very simple database requirements (e.g. for the business customers of an ISP). Now I had one database framework to do both.

Doesn't satisfy every need, but it does one heck of a lot and takes care of all the low-hanging fruit. Like most developers who have been working for decades, it may have taken me that long to arrive at, but recreating it from scratch could be done very quickly.

I basically manage 2 EAV databases - a production one with the 7 tables (discussed) and a development one with the same 7 tables, where I will do imports, migrations, and such. One thing that is truly chaos is when you have hundreds of "virtual" EAV tables with 10's of millions of records (or more) in just 7 tables .... and the house of cards comes down for whatever reason (master-master replication failure, clustering failure etc). Gives you a whole new appreciation of the beauty of a simple flat file database.