The Problem
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.
Tried Solutions
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.
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.
Summary
I’ve provided the above for context. In simple terms, this comes down to:
Imagine:
Field 1table has 1 valueField 2table has 10 valuesField 3table has 4 valuesField 4table has 80 values
Due 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.
Examples
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.