Level 75
Are indexes setup the same?
1 like
Dec 6, 2024
10
Level 3
Query with relationship very slow
Everything was working normally until the 'Estabelecimentos' table in the database was corrupted, and I deleted it and created it again. After that, this simple query started to run very slowly:
$estabelecimento = Estabelecimento::with(['socios'])->where('cnpj_inteiro', $slug_cnpj)
->first();
the cnpj_inteiro column as well as the id in the 'socios' table are properly indexed. But I can't understand why it's taking so long to load. It's taking about 20 seconds to load. I did a test without using 'with' and it returns quickly, but with it it's slow.
Can anyone tell me what's going on?
note: I've already restarted the server and database and it didn't work.
Level 3
Yes exactly the same way and directly in phpmyadmin, here: https://prnt.sc/UJiuIHyknotS
The only observation is that I did not perform the migration of tables with Laravel. I created them directly and manually, they do not have foreign keys, only the index. But as I said, everything was working fine, until I deleted and inserted the "Estabelecimento" and "Empresas" table again.
Level 1
What is the relationship of 'socios' to 'Estabelecimento'?
1 like
Level 3
Of "Socios" with "Estabelecimento" none, but of "Estabelecimento" with "Socios", it is this:
public function socios()
{
return $this->hasMany(Socio::class, 'cnpj_base', 'cnpj_base');
}
Level 73
I suggest running an EXPLAIN on the query to see what and if it is using indexes, my guess is that it doesn't. This might shed some light on your issue.
https://tray2.se/posts/properly-formed-foreign-keys-are-your-best-friends
3 likes
Level 3
I ran an Explain and the query returned very quickly. This was the result:
Level 3
In my "Estabelecimentos" model I had this line:
protected $primaryKey = 'cnpj_base';
After removing it, it went back to normal and the query became fast again. The strange thing is that it already existed before. But after deleting the table and inserting it again, it became slow, and after removing this line, everything went back to normal.
I don't understand why, can anyone give an explanation?
Level 1
Did u try to set cnpj_inteiro as primary kay in Estabelecimentos table? . Another thing to do, Is search if theres a migration where this tablet was create to validate if your actual config Is the same
Level 122
my guess is that you are breaking eloquent rather than SQL. If there is no primary key on the related model, its perhaps not keying the related model correctly and having to use where() when joining the collections together.
A danger from straying from accepted conventions is that you can run into edge cases not encountered by most.
1 like
Level 4
Já tentou limpar os caches? Não sei se isso influencia em projetos em produção, caso não esteja pode executar, se estiver por favor leia a documentação do laravel.
Have you tried clearing the caches? I don't know if this influences projects in production, if not you can run it, if so please read the Laravel documentation.
php artisan optimize
Please or to participate in this conversation.