Carbon consistently slows down my queries. Am I doing something wrong?

This is my query:

$results = RawScoreboard::where('season', $season)
            ->where('week', $week)
            ->where('season_type', $season_type)
            ->get();

My table has a unique key setup for ['season', 'week', 'season_type'] and this query when manually executed in Workbench takes about 30 ms.

My table has timestamps.

When timestamps are enabled, and I run this-query-only in a route, and call that route in my api from insomnia/postman, it takes 3 full seconds to return back a json that has 16 small 3 line json-objects in it.

When I put:

public $timestamps = false;

inside the Model class the same exact route returns the same data minus the created_at and updated_at fields in 40ms.

This has happened to me throughout this project as I have created my API application.

Every tutorial I saw early on from Jeffrey and from Luke seemed to always allude to wishing I had included timestamps on each table whenever I decided not to, so I just have timestamps on all the tables! 😄

But I find I have to turn them off to get simple queries to run quickly. Carbon causes it to slow to a grind almost always for me. Have I done something inherently wrong with how I setup things possibly?

I used default migrations with $table->timestamps() etc.. Running MySql 5.7+ and Php 8.2.33 locally.

LaryAI

1 year ago

Level 58

The issue you're experiencing with Carbon and timestamps slowing down your queries is likely due to the way Eloquent handles date casting. When you have timestamps enabled, Eloquent automatically casts the created_at and updated_at fields to Carbon instances. This casting process can add overhead, especially if you're dealing with a large number of records.

Here are a few suggestions to help mitigate the performance impact:

Eager Loading: If your query involves relationships, ensure you're using eager loading to reduce the number of queries executed. This won't directly affect the Carbon issue but can improve overall performance.
Disable Date Casting: If you don't need the created_at and updated_at fields as Carbon instances, you can disable date casting for these fields. This will prevent Eloquent from converting them to Carbon objects, which should improve performance.

You can do this by overriding the $dates property in your model:
```
protected $dates = [];
```
This will stop Eloquent from automatically casting any date fields to Carbon instances.
Custom Date Format: If you still need the timestamps but want to avoid the overhead of Carbon, you can customize the date format to return them as strings:
```
protected $dateFormat = 'Y-m-d H:i:s';
```
This will ensure that the timestamps are returned as strings, which can be faster than converting them to Carbon instances.
Selective Timestamps: If you don't need timestamps for certain operations, you can selectively disable them for specific queries:
```
$results = RawScoreboard::withoutTimestamps()->where('season', $season)
            ->where('week', $week)
            ->where('season_type', $season_type)
            ->get();
```
Note that withoutTimestamps() is not a built-in Eloquent method, so you would need to implement this logic yourself, possibly by using a query scope or a custom query builder.
Profile and Optimize: Use Laravel's query logging or a tool like Laravel Telescope to profile your queries and identify bottlenecks. This can help you pinpoint exactly where the slowdown is occurring.

By implementing these strategies, you should be able to reduce the performance impact of Carbon and timestamps in your application.

FrancescoDiMuro

1 year ago

Level 1

@jimnayzium No idea why without timestamps the query run faster, but you can refactor the query with an array of conditions more than chaining them, so it would be something like this:

$results = RawScoreboard::where(
    [
        ['season', $season],
        ['week', $week]
        ['season_type', $season_type]
    ]
)->get();

JimNayzium

1 year ago

Level 2

This is has happened since the very beginning of my installation with Laravel 11, which is when I began learning Laravel last year. I have run the simplest of example queries you could think of and then simply removed the timestamps and it instantly changes it all.

I am curious if there is a problem somehow with my configuration maybe or maybe with my local environment.

Please or to participate in this conversation.