[eluser]mvdg27[/eluser]
Hi guys,
I recently started using Datamapper ORM, mainly to experiment a bit with the concept. I'm now building a test application, that is basically an issue tracker, where issues can belong to a project, can have a status a type and category and have one member responsible for the issue.
Now I wanted to produce a list of the issues that match a certain combination of parameters (status, type responsible etc.). The query itself is working properly, but it's slow .. really slow, up to 1.8 secs, with only 25 issues in the database. Am I doing something fundamentally wrong? Are there ways to optimize the queries?
My 'get_issues' function looks like this:
Code: function get_issues($user_id = NULL, $project_id = NULL, $statuses = false, $types = false, $categories = false, $responsibles = false) {
$this->CI->load->model('issue');
$this->CI->load->model('project');
$oIssue = new Issue();
$oProject = new Project();
$oProject->where('id', $project_id)->where_related_member('id', $user_id)->get();
$oIssue
->where_related($oProject)
->where_in_related_status('id', $statuses)
->where_in_related_type('id', $types)
->where_in_related_category('id', $categories)
->where_in_related_responsible('id', $responsibles)
->order_by('updated', 'DESC')
->get_iterated();
if($oIssue->exists()) {
return $oIssue;
}
else {
return false;
}
}
And it produces the following MySQL query:
Code: SELECT `issues`.*
FROM (`issues`)
LEFT OUTER JOIN `issues_projects` issues_projects ON `issues`.`id` = `issues_projects`.`issue_id`
LEFT OUTER JOIN `issues_statuses` issues_statuses ON `issues`.`id` = `issues_statuses`.`issue_id`
LEFT OUTER JOIN `issues_types` issues_types ON `issues`.`id` = `issues_types`.`issue_id`
LEFT OUTER JOIN `categories_issues` categories_issues ON `issues`.`id` = `categories_issues`.`issue_id`
LEFT OUTER JOIN `issues_users` responsible_issues_users ON `issues`.`id` = `responsible_issues_users`.`responsible_issue_id`
WHERE `issues_projects`.`project_id` = 1
AND `issues_statuses`.`status_id` IN ('1', '2', '3', '4', '5', '6', '7')
AND `issues_types`.`type_id` IN ('1', '2', '3')
AND `categories_issues`.`category_id` IN ('2', '1')
AND `responsible_issues_users`.`responsible_id` IN ('1', '2')
ORDER BY `issues`.`updated` DESC
I hope someone can help me find the bottleneck here!
Thanks in advance!
[eluser]cwt137[/eluser]
Most likely something is not indexed. Copy the query that is produced and use EXPLAIN to figure out the slow parts of the query. That will give you some hints.
[eluser]WanWizard[/eluser]
If you want all this in a single result there's nothing wrong with this query.
You have a lot of many to many relations here that you query. Datamapper correctly determines it can query the relationship tables. Do you have indexes on all these id columns?
Copy and Paste this query into PHPMyAdmin or some other interface, and prefix the select with 'EXPLAIN EXTENDED'. The output will tell you exactly where an index is used, where a table scan was needed, if temporary tables needed to be used, etc.
If everything is optimized and it still takes this long, I wonder on which platform your database server is running, since 1.8sec for a query like this isn't normal...
[eluser]mvdg27[/eluser]
Hi WanWizard, thanks for your reply! I wasn't doubting Datamapper producing the correct query, just confused as to how this query could take so long. Now it's clear, I'm missing indexes. I was convinced I had added indexes, but it turns out I didn't. That explains a lot!
Just to be sure: it's best to have a primary key on the id column, and a normal index on the other columns (like issue_id and status_id), right?
[eluser]mvdg27[/eluser]
It worked, back to normal times for the query:
Code: Showing rows 0 - 23 (24 total, Query took 0.0022 sec)
Thanks for the help 
[eluser]WanWizard[/eluser]
[quote author="mvdg27" date="1295053636"]Just to be sure: it's best to have a primary key on the id column, and a normal index on the other columns (like issue_id and status_id), right?[/quote]
Check.
[eluser]mvdg27[/eluser]
Hi Wanwizard,
I have one additional question on optimizing my queries. When I activate the profiler, I see that there are still a lot of queries made while retrieving the issues (100+). They are all small and fast queries, but it doesn't sound very efficient.
I've narrowed down the source of the queries. In my view file, where I build up a table of issues, I want to display the actual status name, instead of the id. And same for the category, type and responsible.
At this point I do it like this:
Code: foreach($issues as $issue) {
$issue->responsible->get();
$issue->type->get();
$issue->status->get();
$issue->category->get();
$data['id'] = $issue->id;
$data['title'] = $issue->title;
$data['responsible'] = $issue->responsible->username;
$data['status'] = $issue->status->name;
$data['type'] = $issue->type->name;
$data['category'] = $issue->category->name;
$this->load->view('projects/issue_row', $data);
}
This means that for each issue, again 4 queries are called. Is there a way to get these results all at once when I select the list of issues from the database?
Thanks again!
[eluser]WanWizard[/eluser]
You can use include_related() to include a column of a related table in the query result.
Code: //instead of
$issues->get();
// do
$issues->include_related('responsible', 'username')->include_related('type', 'name', 'type')->include_related('status', 'name', 'status')->include_related('category', 'name', 'cat')->get();
This will add the columns 'username', 'type_name', 'status_name' and 'cat_name' to the query result, and will make all these queries no longer necessary.
[eluser]mvdg27[/eluser]
Works like a charm! Thanks
|
