Level 54
if you are ordering by it, then creating an index for it is probably a good idea.
3 likes
Jul 2, 2021
6
Level 13
Is there any benefit to indexing 'created_at' column?
I'm currently facing an issue where I'm getting SQLSTATE[HY001]: Memory allocation error: 1038 Out of sort memory, consider increasing server sort buffer size error in MySQL 8
The query is pretty simple -
Post::where('type', 'threads')->orderBy('created_at')->cursorPaginate(20);
Note that, the created_at column is not indexd. If I change to ->orderBy('id'), then the error goes away. Upon some researching, I found out that MySQL 8 has this bug that throws memory error if the database has json column. [ Ref: https://bugs.mysql.com/bug.php?id=103225 ]
In production, I'm planning to use either MySQL or Postgres; but wondering if there's any merit to indexing created_at column?
Level 13
Is it a normal practice to index the timestamps? I've never used it before; but I might start using it.
Level 11
Yep, it absolutely is recommended and normal to index the timestamp columns when you query on them!
Level 13
I index my timestamps when I know I will query/sort by that column often.
2 likes
Level 73
As a rule of thumb don't index anything that you don't use in your queries for filtering or sorting. Too many indexes can make the situation worse than without indexes.
1 like
Level 8
First of all, I would suggest installing debugbar: https://github.com/barryvdh/laravel-debugbar
Then (as others suggested), index whatever it is that you order/filter by. Minimize memory usage by selecting only required fields. Observe these changes using your debugbar.
2 likes
Please or to participate in this conversation.