Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – MySQL – Fetching From 100 Tables With Good Performance

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.

Example Table Layout

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.

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.

Summary

I’ve provided the above for context. In simple terms, this comes down to:

Imagine:

  • Field 1 table has 1 value
  • Field 2 table has 10 values
  • Field 3 table has 4 values
  • Field 4 table 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.

Field Table Example

Imagine the other 3 fields in the example are similar. but with their own column names.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    every field is its own MySQL table

    Gag! Puke! Vomit! Barf!

    You need a single table with 4 columns and 95 rows. If the framework does not provide that, the trash it!

    I don’t see how your second image explains your situation. (And please use text, not images.)

    If entity_id refers to the Entities table, then

    SELECT e.entity_name, m.bundle, ...
    FROM main_table AS m
    JOIN Entity AS e  ON main_table.entity_id = Entities.id
    JOIN Opex AS o ON ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search