Level 102
Hacks no. But I always start with an explain to understand how the query is done. Most likely you can speed it up with some proper indexing
Can you show what you have now? Maybe we can spot some things that can be optimized
1 like
Sep 12, 2022
22
Level 3
Any hacks or speeding up a query with multiple joins?
This is a more theoretical question. I have multiple data tables: users, user_details, projects, project_details,...
I need to make an overview page, where I would paginate by users. In past, I would create eloquent relationships, and pull the data. But, on this overview page, sorting needs to be done on columns, which are spread across every mentioned table.
Because of that, I decided to use joins. Because of that, the query time became very long. Are there any hacks, or tips, that you experienced guys would know to speed up the query? I have used the select command, to exclude some columns, but the time did not change as much. And, I have also played with the pagination number.
Any tips are greatly appreciated.
Level 3
@Sinnbeck Posted my work. example code and a quick DB sketch, below :)
Level 75
Consider reading the chapter in the MySQL manual that covers indexing.
Also look at using related data instead and consider only using join and group by in things like monthly reports.
In a huge database joins are going to be slower.
1 like
Level 73
A join isn't necessarily slow if you have the correct constraints between the foreign key and the primary key of the table you need to join in. You should tell it what to select since you never should pull more than you need to show. The foreign key constraint is an index to querying on it will be very fast, and if it is slow you can use an inline view to do the join after you filtered the result. There are many ways to speed up a query, and the proper indexes are a good start just as @jlrdw says.
1 like
Level 3
Here is the "similar" code example. Each table holds approx 7k of entries, and the EAV one up to 17k.
$usersQuery = User::with(["category"]);
#CODE DELETED FROM 3rd PARTY INTERVENTION
$users = $usersQuery->paginate();
Level 102
@ivymasterman that's quite hard to read. But maybe replace ->paginate() with ->dd() and post the query
Level 73
@ivymasterman The issue isn't with the joins, it's with the LIKE among other things. Truth to be told that is one of the most FUBAR queries I've ever seen, and I write SQL for a living. As @sinnbeck said, give us the generated SQL, we can't possible try to decipher that mess. You are most likely missing quite a few indexes, an EXPLAIN on the generated SQL would be good to share as well, so we can see what the execution plan is, and give hints from that.
1 like
Level 3
Is this oke?
select
users.id,
users.entry_key,
users.company_id,
users.company_entry_key,
users.first_name,
users.last_name,
users.private_email,
users.private_tel,
users.private_mob,
users.private_fax,
users.private_address,
users.private_city,
users.private_zip_code,
users.private_country,
users.business_email,
users.business_tel,
users.business_mob,
users.business_fax,
users.business_address,
users.business_city,
users.business_zip_code,
users.business_country,
users.user_category_id,
users.company_position,
users.company_department,
users.created_at,
users.updated_at,
max(last_user.usered_at) as last_contacted_at,
max(last_user_by_initiator.usered_at) as last_contacted_by_me_at,
companies.name as company_name,
user_additional_infos.status as status
from `users`
left join `user_communications` as `last_user` on `last_user`.`recipient_id` = `users`.`id`
left join `user_communications` as `last_user_by_initiator` on `last_user_by_initiator`.`recipient_id` = `users`.`id` and `last_user_by_initiator`.`initiator_id` = ?
left join `companies` on `companies`.`id` = `users`.`company_id`
left join `user_additional_infos` on `users`.`id` = `user_additional_infos`.`user_id`
group by `users`.`id`
order by `first_name` desc
Level 3
@Tray2 This one hits hard :(. It started of okay, and then patch here and there, and without proper background SQL knowledge, it lead to this
Level 73
@ivymasterman The join isn't the issue, but that code looks fine to me. Run an explain on the query and it will tell you what takes time, and which parts might need an index.
Have you created foreign key constraints between the tables you have joined?
Level 102
@ivymasterman ok so there are no LIKE queries in that? So still slow without any search?
Level 3
@Sinnbeck Yes, this is the query without any search. It takes about 12 s, measured with "microtime()" php function. Since all tables have around 6k, and one up above 15k of records as I mentioned, is it the case that this time is actually average, and not slow?
Level 102
@ivymasterman ouch. Try running it inside your database manager with EXPLAIN in front
1 like
Level 73
@ivymasterman I suggest giving these three a read
https://tray2.se/posts/sqlerrm
1 like
Level 102
I'm curious how you are able to groupBy user ID, but select a ton of other routes? Did you disable strict mode? Also look into if you can write the query without needing grouping when you need that many columns
Level 3
@Sinnbeck Yes, I disabled the strict mode. I had multiple entries in the user_communications table, and I wanted to get the most recent one if the initiator_id condition is met. So i did the left join, and grouped everything by userID, and selected the max(last_user.usered_at) column. This usered_at column is a typo, it is the "last_contacted_at" table, a DateTime type.
Level 102
@ivymasterman if you remove that in your database manager, does it run a lot faster? A sub query select might be faster
Level 3
@Sinnbeck Before removal, it returns an error, that is why I disabled it.
Level 102
Level 3
@Sinnbeck Just to show the explain result, from console:
Illuminate\Support\Collection^ {#1362
#items: array:5 [
0 => {#1360
+"id": 1
+"select_type": "SIMPLE"
+"table": "users"
+"type": "ALL"
+"possible_keys": null
+"key": null
+"key_len": null
+"ref": null
+"rows": "32073"
+"Extra": "Using temporary; Using filesort"
}
1 => {#1359
+"id": 1
+"select_type": "SIMPLE"
+"table": "last_contact"
+"type": "ref"
+"possible_keys": "user_communications_communication_recipient_id_index"
+"key": "user_communications_communication_recipient_id_index"
+"key_len": "9"
+"ref": "bmt_app.users.id"
+"rows": "1"
+"Extra": ""
}
2 => {#1364
+"id": 1
+"select_type": "SIMPLE"
+"table": "last_contact_by_initiator"
+"type": "ref|filter"
+"possible_keys": "user_communications_communication_initiator_id_foreign,user_communications_communication_recipient_id_index"
+"key": "user_communications_communication_recipient_id_index|user_communications_communication_initiator_id_foreign"
+"key_len": "9|9"
+"ref": "bmt_app.users.id"
+"rows": "1 (0%)"
+"Extra": "Using where; Using rowid filter"
}
3 => {#1365
+"id": 1
+"select_type": "SIMPLE"
+"table": "companies"
+"type": "ALL"
+"possible_keys": "PRIMARY"
+"key": null
+"key_len": null
+"ref": null
+"rows": "759"
+"Extra": "Using where; Using join buffer (flat, BNL join)"
}
4 => {#1366
+"id": 1
+"select_type": "SIMPLE"
+"table": "user_additional_infos"
+"type": "ref"
+"possible_keys": "user_additional_infos_user_id_foreign"
+"key": "user_additional_infos_user_id_foreign"
+"key_len": "9"
+"ref": "bmt_app.users.id"
+"rows": "1"
+"Extra": ""
}
]
#escapeWhenCastingToString: false
}
Level 3
@Sinnbeck I will give it a read, and myb start from scratch haha, we will see
Level 3
Adding indexes helped. Added multiple column index on the "user_communications" table, for "recipient_id" and "initiator_id". Thx to everyone, you guys rock!
Please or to participate in this conversation.