Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

JimNayzium's avatar

Eloquent or Carbon causing very slow queries

Through a ton of frustration and more testing attempts than I care to admit, I have narrowed down my issue to one having to do with Carbon and timestamps with the Eloquent query builder.

I have a table that has timestamps, and has an index of (season, week) set in the mysql table.

I run one query raw and one with the Eloquent Query Builder to compare. There is a massive difference in the time it takes to get back the data to Postman.

This particular table has no relationships defined.

Through many debugging attempts it seemed the problem was getting bogged down in the Carbon conversions of the timestamps as the resulting record sets are 5-6000 rows.

When the Model class has:

protected $dates = [];
public $timestamps = false;
protected $hidden = [
        'created_at',
        'updated_at',
    ];

// all of these mimic the column type in the actual table
protected $casts = [
        "player_id"        => "integer",
        "espn_game_id"     => "integer",
        "play_id"          => "string",
        "increment_id"     => "integer",
        "injury_on_play"   => "integer",
        "participant_type" => "integer",
        "play_type_id"     => "integer",
        "score_value"      => "integer",
        "scoring_play"     => "integer",
        "season"           => "integer",
        "stat_yardage"     => "integer",
        "week"             => "integer",
    ];

Query #1

$liveData = LivePlayByPlayParticipant::where("season", CURRENT_NFL_SEASON)
												    	         ->where("week", CURRENT_NFL_WEEK)
    	    	    	 										->get();

Query #1 with the above in the Model: Takes 15 seconds to send back 6000 rows of data from 200,000 total rows to Postman.

When I remove the above things from the Model class, Query #1 still takes 4 seconds.

Query #2

  $liveData = DB::select("SELECT * 
                                		 FROM live_play_by_play_participants
                               			 WHERE season = ? AND week = ?", 
										[CURRENT_NFL_SEASON, CURRENT_NFL_WEEK]);

Query #2 takes 5-600 milliseconds.

I then created a copy of my entire table, and deleted the timestamp columns all together. I setup a default model class with no bells or whistles.

Query #3 on New Table with no timestamps

        $liveData = LivePlayByPlayParticipantsBall::where("season", CURRENT_NFL_SEASON)
            ->where("week", CURRENT_NFL_WEEK)
            ->get();

Query #3 on a table with no timestamps took 4 seconds to return the data.

So the timestamps cause it to take 15 seconds. Query Builder causes it to take 4 seconds with timestamps set to false in the Model. Query Builder causes it to take 4 seconds on a duplicated table without timestamps present. RAW DB query takes 500 milliseconds.

Is there any way to use Query builder and Eloquent more effectively than I am doing? I am new to laravel and just put timestamps in all my tables based on a few other tutorials I did when I set it all up. I have tons and tons of queries like this in my application that I have converted to manual queries to optimize the load times and it seems to have changed the speed drastically! But it's a bummer to not be using the wonderfully elegant Laravel goodness.

What am I doing wrong?

0 likes
16 replies
LaryAI's avatar
LaryAI
AI
1 year ago
Level 58

The issue you're experiencing with Eloquent is likely due to the overhead of model hydration and Carbon date conversions. When you use Eloquent's get() method, it retrieves the data and converts each row into an instance of your model, which can be resource-intensive, especially with large datasets.

Here are some strategies to improve the performance of your Eloquent queries:

  1. Use select to Limit Columns: If you don't need all columns, specify only the ones you need. This reduces the amount of data being processed.

    $liveData = LivePlayByPlayParticipant::select('column1', 'column2')
    ->where('season', CURRENT_NFL_SEASON)
    ->where('week', CURRENT_NFL_WEEK)
    ->get();

  2. Use chunk for Large Datasets: If you need to process a large number of records, consider using chunk to process them in smaller batches.

    LivePlayByPlayParticipant::where('season', CURRENT_NFL_SEASON)
    ->where('week', CURRENT_NFL_WEEK)
    ->chunk(1000, function ($participants) {
        foreach ($participants as $participant) {
            // Process each participant
        }
    });

  3. Disable Timestamps: Since you mentioned that timestamps are not needed, ensure they are disabled in your model, which you have already done.

  4. Use toArray or toJson: If you don't need Eloquent models and just need the data, you can convert the collection to an array or JSON, which might be faster.

    $liveData = LivePlayByPlayParticipant::where('season', CURRENT_NFL_SEASON)
    ->where('week', CURRENT_NFL_WEEK)
    ->get()
    ->toArray();

  5. Use DB::table for Query Builder: If you don't need the full power of Eloquent, consider using the query builder directly, which is more lightweight.

    $liveData = DB::table('live_play_by_play_participants')
    ->where('season', CURRENT_NFL_SEASON)
    ->where('week', CURRENT_NFL_WEEK)
    ->get();

  6. Profile and Index: Ensure your database is properly indexed. You mentioned having an index on (season, week), which is good. Use tools like Laravel Telescope or database profiling to identify bottlenecks.

  7. Optimize Database Configuration: Sometimes, the database configuration itself can be a bottleneck. Ensure your MySQL server is optimized for the workload.

By applying these strategies, you should be able to improve the performance of your Eloquent queries while still leveraging Laravel's features. If performance is still an issue, using raw queries as you have done might be necessary for specific cases.

JimNayzium's avatar

This also took the same 4 seconds as the others described above.

        $liveData = LivePlayByPlayParticipant::select([
            'id               AS id',
            'player_id        AS pid',
            'season           AS yr',
            'week             AS wk',
            'espn_game_id     AS gm',
            'play_id          AS ply',
            'participant_type AS typ',
            'increment_id     AS inc',
            'play_type_id     AS ptp',
            'score_value      AS pts',
            'stat_yardage     AS yrd',
            'scoring_play     AS scr',
            'injury_on_play   AS inj'])

            ->where("season", CURRENT_NFL_SEASON)
            ->where("week", CURRENT_NFL_WEEK)
            ->get()
            ->toJson();
Tray2's avatar

@JimNayzium This is a classic case of pulling more data than you need, you should only get the columns you need, that means that you almost never do

Model::where('column1', $value)->get()

But rather do

Model::where('column1', $value)->select('column1', 'column2', 'column3')->get();

This is more important when fetching bigger amounts of records, if you are fetching 10 records, it has less impact, but since you are fetching 6K, well then it's super important.

And, if you don't care about the timestamps, you can put them in a hidden array in your model.

 protected $hidden = [
        'created_at',
        'updated_at',
    ];
experimentor's avatar

@jimnayzium Interesting findings. Everyday I learn something new here. So as @laryai said and you established, Eloquent's get() method converts each row into an instance of the model. 6000 rows, with Carbon handling timestamps caused the huge delay of 15 seconds.

When you removed the timestamps, by using "select" or a table without timestamps, the time came down to 4seconds because, carbon is no longer needed. But 6000 rows still need to be converted to model instances. In the raw query, this conversion is not needed. Hence it is fast - 500 ms.

This is what I understood so far. Maybe I'm wrong, but this seems to be the most plausible explanation to me. Hope some expert will be able to corroborate this.

Coming to optimisation of your Laravel application: Why are you retrieving 6000 rows at a single time? Could you not paginate the results, or aggregate them?

If it is for display of the records, paginate() method may be helpful. If it is for some sort of analysis, better to do the analysis in a background job and fetch the results on demand.

My reasoning: I have built a CRM with millions of customer rows. Each agent is assigned thousands of customers. When we want to show the assigned customers to an agent, I could write an eloquent query and return thousands of rows which may take more than 5 seconds. But what would the agent do looking at 6000 rows of customers in a single view? It would make sense if they see a list of 10 (probably ordered by some column). Hence paginate works here.

Maybe your application needs the heavy data on demand. In that case, I think the raw db query is the best option left. If not, please do consider pagination or aggregating with sql views or background jobs.

Hope this helps.

JimNayzium's avatar

@experimentor This is all super helpful insight so I appreciate your taking the time to respond!!

In my case, I am building an application that serves the purpose of delivering data to a fantasy football game which retrieves and shows the live on-going data to the contestants of the individual contests, so each NFL player has live, accumulating statistics on any given game-day.

The client side retrieval will have it's own set of scoring methods determined by each individual league's settings, so the raw result data needs to be retrieved every minute or so to record the ongoing changes etc...

At the beginning of the game day on Sunday there are no rows, but by mid day Sunday there are about 3000 entries into the statistical table which tracks everything, rusher, passer, tackler, etc.. on any given play for all 12-16 games that day etc...

I am working on how to store it all on the client side so the users only need to retrieve the newest plays that their individual view has not yet downloaded, so that will make each subsequent retrieval more optimized and only be returning the latest entries in the last one minute, but that won't address the person who opens the app for the first time on Sunday night and then views all the results then.

Or when the person opens the app on a non-game day, and hadn't opened it during the game, and then has to load it all at once the first time etc...

I am also optimizing the views on the client side to only ASK for player_ids that are necessary at the page of viewing, but some pages are like statistical leader boards that would indeed need pagination but typically in fantasy football you are viewing dozens of players at a time, and each player may have dozens and dozens of rows of data in the queried table, so it gets large quickly.

I am open to ideas on how to better handle it all, but I think it's one of those scenarios where the "exceptions" to some of the rules will apply.

Yet another idea I was having was to do more calculations on the server side during the cron-jobs that accumulate these rows to begin with, so I could maybe built a table of player objects that included longtext fields with all the aggregated rows of data already loaded into those columns as json strings, and maybe the laravel API i have built could do that in addition to what it's doing, so in essence then the queriies would deliver the same exact json contents, but deliver it in a much less query heavy way if that makes sense?

That's what I am working on now as a test, but may prove to make the cronjobs themselves too intense etc...

maxxd's avatar

@JimNayzium I would look into storing interim data in Redis and pulling it from there. If the data doesn't have to persist from week to week, you could probably get away with only using Redis, though I think it makes sense to load the data into both short-term (Redis) and long-term (MySQL) storage.

1 like
JimNayzium's avatar

@maxxd Appreciate your input. I have started the deep dive into learning more about Redis!

Lumethys's avatar

@JimNayzium any reason why you dont use aggregate table for analytics? Or any reason why the calculation cannot be done on server-side (where you can use YourModel::where(....)->lazy()) and streamed with websocket?

maxxd's avatar

@Lumethys

Aggregate tables still require a DB hit which could potentially be quicker depending on the data being pulled, but really any speed updates would be due to not having to do the calculations. And it could potentially serve errant data depending on the last update time. Which, to be fair, is also a hazard of using Redis but Redis is specifically built for fast crud operations meaning you can pretty seamlessly update the cache every time a change is made, keeping your data real-time.

Lumethys's avatar

@maxxd well the points of aggregate tables is to reduce the calculation time and JOINs overhead of on-the-fly raw data query. Instead pre-calculate those so that you only query it specifically for the analytics part. So the fact that there still is a db hit is not a problem.

Also, analytics are not meant to be real-time. It's in the name, you take time to analyze the data, most likely according to predefined template, things like "average order price per month",... what analytics could be done with "data from 2.5 seconds ago til now"? Analytic is what the business department look at at the end of month/ quarter/ year/ predefined business period, not a monitoring tool.

So I dont see any reason that OP need to bring like half the db to the FE continuously. What business problem does that solve? In my opinion, if you want a "auto-updating" graph via websocket, then you could just make an aggregate table and stream that. Sure there is some delay between an action is taken and the aggregate table update, but that is more than acceptable in virtually every cases that is "analytics"

In any case, we can only pick 1, either performant, or true real-time data calculation.

maxxd's avatar

@Lumethys I'm not gonna disagree with any of your points. In my current position, "analytics data" are used as a rate limiter in certain situations which may be a semantic difference or a flat-out bit of a bastardization of the term, but we're still analyzing the data on a more frequent basis hence my personal preference for Redis. No biggie, 9 times out of 10 either will work, and we have used aggregate tables quite a lot historically. Some of those are being refactored to Redis due to the frequency of data comparison that I mentioned earlier, but for the most part they have and continue to serve us well.

I'm not entirely sure what "bring like half the db to the FE continuously" means - could you elaborate?

Lumethys's avatar

@maxxd OP mention that his approach is load all of the relevant data (with many joins from other comments), and this has to be "live", meaning that the client side will need to continuously polling for data (i didnt see any websocket mentioned). Which is pretty much "brings like half the db to the FE continuously" for me, not literally half, but you get the idea.

If OP want "true real-time" analytic that is calculated on-the-fly, then there is simply no way to make it performant. However, i dont think OP truly need it to be that way, hence my question: "any reason why dont you do..."

As for you, i dont entirely understand why you need to "migrate from aggregate table to redis". Redis is a cache, and whether or not it is used should not affect your db structure, if you want redis, just slap it in front of the aggregate table? Or if the ingest is the bottleneck, take a look at Laravel Pulse's (which is basically just aggregate tables) Redis Ingest feature

azimidev's avatar
$liveData = DB::table('live_play_by_play_participants')
    ->select([
        'id AS id',
        'player_id AS pid',
        'season AS yr',
        'week AS wk',
        'espn_game_id AS gm',
        'play_id AS ply',
        'participant_type AS typ',
        'increment_id AS inc',
        'play_type_id AS ptp',
        'score_value AS pts',
        'stat_yardage AS yrd',
        'scoring_play AS scr',
        'injury_on_play AS inj'
    ])
    ->where("season", CURRENT_NFL_SEASON)
    ->where("week", CURRENT_NFL_WEEK)
    ->get()
    ->toJson();

how about this?

JimNayzium's avatar

@azimidev This was faster too, but at this point I made some changes to the Models to not use Carbon and different things that I "sort of lost track of" so I am not 100 percent sure which combination fixed it. All I know is any time the Carbon class needs to convert thousands of dates it slows to a grinding halt noticeably, which I am sure is most likely due to something I am doing, but the work arounds have solved it for now!

Snapey's avatar

dont forget indexing. 500ms is still slow.

Snapey's avatar

make sure you dont have any debugging turned on, like debugbar or telescope

Please or to participate in this conversation.