I appreciate your interest. On about my 2nd iteration of things I relied on MySQL Views for queries, but I felt that kind of defeated the purpose (in being truly data independent), so I wrote my own database and eav crud library (initially in PHP now refactored in production MVC in PHP 7.4 and a development version under way in node js). The library was pretty ugly at first for the SELECT but elegant and fast for INSERT, UPDATE and DELETE (as you're basically just doing up to 7 queries that are insert into / update / delete from table where parent_id=xyz). The ugliness with the SELECT initially was that I could not see past using giant IFNULL(MAX(CASE ... statements, which are not great on performance.

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.