To set the scene, I work on a PHP Laravel system that has taken a drupal-style approach where every field is its own MySQL table. Every table has optimum indexing making it very quick to fetch data for a specific record on a table-by-table basis. Each field table could contain 1, or multiple values.
To fetch a record, our system procedurally iterates the fields on a given record and runs one query per field to fetch the data. Each query is lightning-fast and returns values. In the example above, this means 4 queries get run to fetch the field data.
The problem is that some record types might have 100+ fields attached resulting in 100+ queries being run. This works, but the timing can add up to 0.2+ seconds or so to fetch everything.
Not a major issue, but I've been pondering if there's a better way. It feels like it should be possible to fetch this all with a single faster query.
Attempt #1: I've tried using UNION to pass all queries in one go (with some clever logic to standardise select across the board). This returns the right results but seems to take much longer than 100 independent indexed queries.
Example query: https://pastebin.com/WSkvtSRM (procedurally generated, imagine this with 100 field tables).
Attempt #2: I've tried left joins from the record table (and splitting them into batches of 50 fields to avoid the MySQL 61 join limit). While this achieves the speeds I'm looking for, there's an awful lot of data duplication where the joined field tables have multiple values. This requires some sorting PHP-side and feels somewhat fragile.
I've provided the above for context. In simple terms, this comes down to:
Imagine:
Field 1 table has 1 valueField 2 table has 10 valuesField 3 table has 4 valuesField 4 table has 80 valuesDue to the way left joins behave, I end up with 1 * 10 * 4 * 80 rows (3200 rows) in the MySQL response which I have to sift through in PHP to understand the "real" values (95 rows).
Is there a single query I can run just retrieve the 95 table rows I want directly? Either through joins or some other means?
Performance is key. I'm looking for the most efficient approach.
Here's an example of a field table with some test data. In this example, there's some peripheral data about the record it belongs to and when it was created. I need the opex_id, currency_code, forecast, value and comment columns that make up this field payload.
Imagine the other 3 fields in the example are similar. but with their own column names.
For my specific issue, stiching together the queries via a UNION (as described in Attempt #1) was the best solution I could find. There were time savings by making a single DB request (instead of 100 seperate ones).
My issue in Attempt #1 above turned out to be a Laravel Query Builder issue (and not MySQL): https://laravel.com/docs/queries
The time it took Laravel QB to UNION together 100 queries far exceeded the time to run them separately.
To solve this, I adapted my code to manually create a query as a string and pass it through. This ended up cutting run-time by up to 50%.
In summary (and probably no suprise), running 100 queries as a UNION is faster than running 100 independent query requests.
To anyone reading this, the DB structure described above is not recommended in most cases. For me, this solution was for a procedural form-builder, where admins can create their own single/multi-value fields via an interface.
Unless there's a specific need for it, you'd be better off with a more traditional single-table approach.
