cloudfoundry / cloudfoundry/cloud_controller_ng
Possible slow query
Nobody has claimed this yet.
- Dominant language
- Ruby
- Stars
- 207
- Forks
- 373
- Avg merge
- 2d 12h
- Merged PRs (30d)
- 56
Description
We (the MySQL team) have received a customer support query about a slow query. The query is taking 18 seconds.
JIRA
The query is doing a large join.
SELECT count(*) AS `count` FROM (SELECT DISTINCT `service_instances`.* FROM `service_instances` INNER JOIN `spaces` ON (`spaces`.`id` = `service_instances`.`space_id`) LEFT JOIN `service_instance_shares` ON (`service_instance_shares`.`service_instance_guid` = `service_instances`.`guid`)) AS `t1` LIMIT 1;
# Time: 2022-09-01T09:04:11.389209Z
# Schema: ccdb Last_errno: 0 Killed: 0
# Query_time: 18.541919 Lock_time: 0.000479 Rows_sent: 1 Rows_examined: 144329 Rows_affected: 0 Bytes_sent: 60
This appears to be the corresponding model
https://github.com/cloudfoundry/cloud_controller_ng/blob/0645a640be62cef0a524164d15aaf93957ee5458/app/models/services/service_instance.rb#L37
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with app/models/services/service_instance.rb at the linked line and the supplied MySQL query and timing data. Reproduce or profile the query against the relevant schema, then identify an agreed change and completion criterion; the issue does not currently define either.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, ruby
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100